Power BI DAX for CALCULATETABLE with FILTER ON PREVIOUSDAY

[Anonymous]

Good day,

I do have below scenario (example)

I would like track ONLY changes compare to prior day (BusinessDate). 

Customer Table

CustomerName	CustomerId
A	1
B	2
C	3

Review Table

CustomerId	BusinessDate	ReviewDate
1	16-Jun-19	24-Jan-18
1	17-Jun-19	25-Jan-19
2	16-Jun-19	19-May-18
2	17-Jun-19	19-May-18
3	16-Jun-19	22-Feb-18
3	17-Jun-19	22-Feb-18

For CustomerId = 1, the ReviewDate has changed on the BusinessDate 17-Jun-2019 from 24-Jan-2018 to 25-Jan-2019.  And the rest of the customers information have not changed. So, it should show " 1 '.

When user selects the date (17-Jun-2019) from the Date slicer, Table / visual should only show " 1 ".

When user selects the date 16-Jun-2019 from the Date slicer, it should show " 0 " since the information has not changed.

Please note: I have used CALCULATETABLE(ReviewTable, FILTER(Calendar, Date = PREVIOUSDAY(Businessday))

 to store previous day information to compare, but didn't work. 

I do not understand why PREVIOUSDAY hasn't worked in that case.

Thank you

[MFelix]

Hi @Anonymous ,

Not sure if this is still needed but try adding a calendar table (no related with other tables) and the following 3 measures to your data model.

Check_Value =
VAR Minimum_Date =
    CALCULATE ( MIN ( Review[BusinessDate  ] ); ALL ( Review ) )
VAR Selected_Date =
    MAX ( 'Calendar'[Date] )
VAR Customer_Selection =
    SELECTEDVALUE ( Review[CustomerId] )
RETURN
    IF (
        Selected_Date = Minimum_Date;
        0;
        IF (
            MINX (
                FILTER (
                    ALL ( Review );
                    Review[BusinessDate  ] = Selected_Date - 1
                        && Review[CustomerId] = Customer_Selection
                );
                Review[ReviewDate]
            )
                <> MAX ( Review[ReviewDate] )
                = TRUE ();
            1;
            0
        )
    )

Validation_Date = IF(MAX(Review[BusinessDate  ]) = SELECTEDVALUE('Calendar'[Date]); 1 ; 0)

Count Changes = 
SUMX(FILTER(Review;[Validation_Date] = 1);[Check_Value])

Now use your count changes has your calculation please check the result in PBIX file attach.

As you can see you can even place it on a bar chart.

[v-lili6-msft]

hi, @Anonymous 

First, you must know that calculated column and calculate table can't be affected by any slicer. 
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Only measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

Best Regards,

Lin

[Anonymous]

Thank you for the reply.

Ok. I would like to find out how many ReviewDates have changed Today when comparing with Yesterday's ReviewDate.

I am looking for suitable DAX.

Thanks