cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nmck86 Member
Member

Dax Measure for Date based on slicer filter

Hi I have a report and I was hoping to get a measure in the report that will filter my data down based on the date. For instance, in the visual below: 

You see I have a date filter for 1/13 through 1/30; however, I want my visuals to dynamically filter down to only show the data on 1/30. PBIX FILE is attached for any help folks can provide. I want Power BI to dynamically filter the data down based on the latest date in the report. PBIX FILE ATTACHED. 

https://drive.google.com/file/d/1L-CLdcrK71zAiYOAlgP_rjSnBdbejPPh/view?usp=sharing 

2019-03-19_16-52-001.png

 

Expected output: Note: in this example i used the date slicer to show you an example of what i want the output to look like. However, I do not want to use the date slicer for that I just want the report to default load the latest date based on the filter applied.

2019-03-19_16-50-292.png

 

2 REPLIES 2
Super User
Super User

Re: Dax Measure for Date based on slicer filter

Hi @nmck86 ,

 

Not sure if I understand the requirements of your post. You want to select a range of dates in the slicer and then get the values only from the last date is selected? Or do you want at any time to show the values presented at the range of dates you select?

 

If you option is the first so although people select a range of dates you will always get the last date you can do it changing the type of slicer so people will be forced to select only one date, or adding a filter to your measures that force the latest date something like:

 

Last Values =
CALCULATE (
    SUM ( Table[Column] );
    FILTER ( ALL ( Table[Date] ); Table[Date] = MAX ( Table[Date] ) )
)

If you want the second option you need to probably use a different approach adding a new slicer that tells you if you get the latest value or the full dates selected then your measures should look similar to this:

 

Last Values =
IF (
    SLicerTable[Column] = "Max Date";
    CALCULATE (
        SUM ( Table[Column] );
        FILTER ( ALL ( Table[Date] ); Table[Date] = MAX ( Table[Date] ) )
    );
    SUM ( Table[Column] )
)

In this case you would need to create another table with a list of values similar to this:

SlicerTable

Max Date

Range of Dates

 

Can you explain a little better the final result you are expecting,

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




nmck86 Member
Member

Re: Dax Measure for Date based on slicer filter

Hi Felix,

 

If you take a look at the pbix file attached I basically want the below to occur... your first formula kind of works but if I use just that field alone it doesnt filter the data down exactly how I need it to. I need for no matter what I select in the date slicer, my data dynamically filter down to the latest date selected in that slicer.

 Below I selected 1/13/2018 through 1/30/2016 and I want only the data from the day 1/30/16 to show up. A top N will do that; however, I was hoping for a dynamic meausre to accomplish it though.

 

2019-03-19_20-34-45a.png