calculated field uses dynamic

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Power BI training and help » Calculated field that uses dynamic filter or any way of doing th

Calculated field that uses dynamic filter or any way of doing th

resolvedResolved · Low Priority · Version 365

Calculated field that uses dynamic filter or any way of doing th

Hi there,

I have a really tricky request in a report and wonder if you could help.

The data has a 'cases' table. The cases table has 'date opened' and 'date closed' date fields.

The whole report needs a filter by date, this would count how many cases are NOT closed within that filtered date range. Once this is done it'd flag the cases that have 'date opened' within the range as 'new'.

We'd then have for example 30 cases between that date range that are still open (date closed not within range) and 5 cases that are new (date opened within that range).

I thought of doing it as a calculated column 'if date opened >= date from and <= date to, cases is 'new' then it counts. But I'm struggling to do this as it'd refer to the filter and not a set date.

Any ideas? I hope It makes sense...

Thank you so much, Grazi

RE: Calculated field that uses dynamic filter or any way of doin

Hi Grazi,

Thank you for your question to the forum.

The scenario you describe does seem complex and would be easier to understand if you were to send us a sample of you dataset to:

info@stl-training.co.uk

Please note your previous question - related to this one - has already been answered with a possible solution. This may help you to solve this question.

Kind regards
Martin Sutherland
(IT Trainer)

RE: Calculated field that uses dynamic filter or any way of doin

Thank you Martin.

I couldn't get it making sense and ok to send on Power BI so I just sent an excel with some dummy data showing with formulas what's needed.

Thank you, Grazi

RE: Calculated field that uses dynamic filter or any way of doin

Hi Martin,

I've emailed it. Did you get it?

He said to post it here instead, but I explained it was to send a file.

Thank you so much, Grazi

RE: Calculated field that uses dynamic filter or any way of doin

Hi Grazi,

As you are using the 'date to' and 'date from' fields inside an IF formula, these fields will be referring to the filter as they are not set dates. Let's say the start date was 01/10/23 and end date was 31/10/23. I would use the DATE function in DAX to refer to set dates so the formula would look something like:

New Cases = IF([date opened]>=DATE(2023,10,1)&&[date opened]<=DATE(2023,10,31),1,0)

I hope this helps to solve your problem

Kind regards
Martin

RE: Calculated field that uses dynamic filter or any way of doin

Hi Martin,

Thanks so much for this. I'll try it,but how would the 01/10/2023 be dynamic? I need it to be referring to the filter that the user will choose rather than having a set date.

New Cases = IF([date opened]>=DATE([slicer / filter date])&&[date opened]<=DATE([slicer / filter date]),1,0)

If that's not possible, I though that maybe I need people to have a 'welcome' screen to select dates before the reports are loaded? 'Q1 2023', 'Q2 2023', etc...

Let me know your thoughts. I'll be playing with it this week.

Thank you so much, Grazi

RE: Calculated field that uses dynamic filter or any way of doin

Hi Grazi,

Thank you or your reply.

What you need to do is to load a 'Date table' from Excel as well as the data containing the case data. This date table can just have 1 column for all the dates in 2023 to be displayed in your slicer ie. all dates from 01/01/23 to 31/12/23. Call this field 'date range' in a sheet called 'Date table'.

1. Make sure there is no relationship between both tables in Power BI desktop by checking the data model.

2. Then create a table with columns for Case ID, Case Date and Date closed

3. Create a slicer based on the dates from the date table

4. Write the following DAX measure to show 1 if any case is within the date range or is still open (ie. where the date closed is blank) or 0 if it's not in the date range:

Within Date Range =

var rangeStart = FIRSTDATE('date table'[Date range])
var rangeEnd = LASTDATE('date table'[Date range])
return
IF(
SELECTEDVALUE(Sheet1[CaseDate])>=rangeStart
&&
SELECTEDVALUE(Sheet1[dateClosed])<=rangeEnd
||
SELECTEDVALUE(Sheet1[dateClosed])=BLANK(),
1,0)

This DAX formula is based on the formula you used in the 'Case within range' field in the top table of the Excel sheet you sent me

5. Finally drag the measure to the table to display a list of 0's and 1's

6. Now change the date range in the slicer to update the list

I hope this is a solution to your problem. Please let me know how you get on

Kind regards
Martin


Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

 

Training courses

 

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Power BI tip:

Scheduled Refresh and Power BI Service

If you're using Power BI Service, set up scheduled refresh for your datasets. This ensures that your reports are always up-to-date with the latest data. Understand the refresh limits imposed by Power BI Service and optimize your data model and queries to stay within those constraints.

View all Power BI hints and tips


Server loaded in 0.06 secs.