topic Day over Day comparison based on selection from date slicer in DAX Commands and Tips

Day over Day comparison based on selection from date slicer

ragnezza — Wed, 29 May 2024 21:24:24 GMT

Hello community, I'm looking for support in a Day over Day comparison based on selections from date slicers.

I have a table with Date and ID, similar to this:

Date	ID
Jan 3	ABC001
Jan 3	ABC002
Jan 3	ABC003
Jan 2	ABC001
Jan 2	ABC002
Jan 1	ABC001
Jan 1	ABC002

Then I have two Date slicers:

Date slicer 1 -> to simply filter records based on the selected Date;
Date slicer 2 -> to create a flag (Yes/No) if the records shown at step 1. are also present in the selected day

In other words, if Date slicer 1 = Jan 3 and Date slicer 2 = Jan 2, the table should show the following:

Date	ID	Flag
Jan 3	ABC001	Yes
Jan 3	ABC002	Yes
Jan 3	ABC003	No

I used the following Calculated Column to get the Flag, but as you can see it's working only with hard coded condition (Date = Date-1), rather than having the Date = SELECTEDVALUE from Date slicer 2:

Flag =
IF( CALCULATE (MAX (Table[ID] ),
ALLEXCEPT( Table, Table[ID] ),
Table[Date] = EARLIER(Table[Date])-1) = BLANK(),
"Yes", "No" )

I struggle to make it work with the SELECTEDVALUE in this formula, so I'm not sure if I have to use another approach (measure, different function,...)?

Any hint/recommendation is much appreciated, thanks!

Davide

Re: Day over Day comparison based on selection from date slicer

Anonymous — Thu, 30 May 2024 08:05:27 GMT

Hi ragnezza

You can try the following measure:

Flag = VAR SelectedDate1 = SELECTEDVALUE ( 'Slicer1'[Date] ) VAR SelectedDate2 = SELECTEDVALUE ( 'Slicer2'[Date] ) RETURN IF ( MAX ( [Date] ) = SELECTEDVALUE ( Slicer1[Date] ), IF ( CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[ID] = MAX ( [ID] ), 'Table'[Date] = SelectedDate2 ) > 0, "Yes", "No" ), BLANK () )

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Day over Day comparison based on selection from date slicer

ragnezza — Thu, 30 May 2024 21:07:16 GMT

Thanks a lot Anonymous this worked!