cancel
Showing results for 
Search instead for 
Did you mean: 
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

Accepted Solutions
Microsoft v-huizhn-msft
Microsoft

Re: Reference Sliced date in expression

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
jreade Frequent Visitor
Frequent Visitor

Re: Reference Sliced date in expression

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.

mattbrice Senior Member
Senior Member

Re: Reference Sliced date in expression

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.

Super User IV
Super User IV

Re: Reference Sliced date in expression

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/
Microsoft v-huizhn-msft
Microsoft

Re: Reference Sliced date in expression

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

Anonymous
Not applicable

Re: Reference Sliced date in expression

@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?

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors