Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Reference Sliced date in expression

I need to create a measure that, as part of its calculation, knows what the date range from the date range slicer is?

 

Each row of data is date and time stamped so I can reeference that, but better would be up until the end of the max date defined by the slicer.

 

Is there anyway that I can calculate the latest date in the current range so that I can use it in another expression?

 

Using MAX returns the max date, but for all data, as the date range slicer doesn't affect the data table.

 

Should I be looking at a calculated table? 

 

Any help much appreciated.

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

Hi @Anonymous,

If you add Table[Date] in slicer, and you select a date range in slicer, you can create a measure using formula below to get the latest date of the range in slicer.

Latest Date in slicer =
CALCULATE ( LASTDATE ( Table[Date] ), ALLSELECTED ( Table ) )


Then you can use the meausre [Latest Date in slicer] in another DAX expression.

Best Regards,
Angelia

View solution in original post

5 REPLIES 5
v-huizhn-msft
Employee
Employee

Hi @Anonymous,

If you add Table[Date] in slicer, and you select a date range in slicer, you can create a measure using formula below to get the latest date of the range in slicer.

Latest Date in slicer =
CALCULATE ( LASTDATE ( Table[Date] ), ALLSELECTED ( Table ) )


Then you can use the meausre [Latest Date in slicer] in another DAX expression.

Best Regards,
Angelia

Anonymous
Not applicable

@v-huizhn-msft

 

This works! Thank you for the suggestion.

 

What I don't understand is, why the need for creating a separate table? Why doesn't it work when I reference [Date] in my main table of data for both the slicer and 'Latest Date is slicer' expression?

 

The problem I now have is that the Slicer no longer affects the content of my report (because it's referencing another table). I could fix this with a relationship (many to one and one way?)

However...

The LASTDATE function insists on have distinct values, which my data does not have.

 

The new, separate [Date].date table is generated with a distinct function, which solves the LASTDATE issue, but now means the data in that table doesn't reference my date ranges I have against the data (which is DD:MM:YY HH:MM:SS)

 

How do I get the slicer to affect the column from which I'm reading Latest Date and affect my main data set?

mattbrice
Solution Sage
Solution Sage

While i'm not 100% sure I understand your need, I believe that you want to look at using the LASTNONBLANK function to retrieve the last date that has data in the data table for the range you selected.

jreade
Frequent Visitor

I think I want to do something very similar. I would like to create 2 multi-row cards of data. I have a relative date slicer. The first card would display the data for the timeframe selected by the slicer, while the second card would show the data from the previous period for the timeframe selected by the slicer. For example, if you selected this 7 days in the slicer the second card would display the 7 days previous. If you selected this 3 months in the slicer the second card would display the 3 months previous. Ideally, you would be able to select any range of dates and the second card would calculate the range selected and determine the timeframe of the previous period.

Hi @jreade,

 

I can give it a try.  Share the link from where i can download your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.