Forum Discussion

ragnezza's avatar
ragnezza
Frequent Visitor
2 years ago
Solved

Day over Day comparison based on selection from date slicer

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:

DateID
Jan 3ABC001
Jan 3ABC002
Jan 3ABC003

Jan 2

ABC001
Jan 2ABC002
Jan 1ABC001
Jan 1ABC002

 

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:

DateIDFlag
Jan 3ABC001Yes
Jan 3ABC002Yes
Jan 3ABC003No

 

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
" Slicer"
Compare
Previous Date
SELECTEDVALUE
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.