How to use max date from slicer in calculations

[Anonymous]

Hi,

I have a report, where I currently have 3 cards showing today's value (distinct count) of three different categories.

I'll like to change the setup, so instead of showing today's values, the cards must show the value from the max date of the date slicer - and also return a blank value if no rows are found on that date.

I tried multiple calculations proposals from this community and others, but I haven't found a solution so far.

The closest I have is the following:

Test =
VAR MaxDate =
    CALCULATE( MAX( 'data'[date] ), ALLEXCEPT( 'data', 'data'[date] ))
VAR Numbers =
    CALCULATE( DISTINCTCOUNT( 'data'[someId] ), FILTER( 'data', 'data'[date] = MaxDate ))
RETURN
    Numbers

But it returns the count from the latest date for each category, not for the max date of the slicer. It's easy to test by returning "MaxDate" instead of "Numbers".

If I use "ALL" instead of "ALLEXCEPT", the max date of the data set is returned, but then the date slicer is ignored.

All data is in one table, and I don't have calendar/date table.

Any ideas?

BR Morten

[Anonymous]

Hi @Anonymous,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[ValtteriN]

Hi,

Here is one way to get the maxdate:

Maxdate =

IF(ISFILTERED('Calendar'[Date]),

CALCULATE(MAX('Visual Calendar'[Date]),'Visual Calendar'[Date]=MAX('Calendar'[Date]),ALLSELECTED('Calendar'[Date])), BLANK())

Here I am returning MAX date of selection if calendar table is filtered.


Example:
without selection:

You don't necessarily need the ALLSELECTED in the dax so it can be removed for same result.

You mentioned that you don't have calendar but I recommend creating one and marking it as a date table, since it is useful for many scenarios and without one your model will create local date tables.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Anonymous]

Hi Valtteri,

Thank you for your answer!

So I need to add a calendar table to the model or how did you set it up? And is the calendar table connected to the other table in the data model?

/Morten