Day over Day comparison based on selection from date slicer

[ragnezza]

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:

1. Date slicer 1 -> to simply filter records based on the selected Date;
2. 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

[ragnezza]

Thanks a lot @Anonymous this worked!

[Anonymous · Accepted solution]

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.