Count how many values have changed between Yesterday and Today

[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 ' (because there is only one change).

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.

[Anonymous]

Hi @Anonymous ,

You can try to use following measure to achieve your requirement:

Measure =
VAR selected =
    MAX ( Calendar[Date] )
VAR reviewCount =
    CALCULATE (
        COUNTROWS ( Table ),
        FILTER ( ALLSELECTED ( Table ), [BusinessDate] <= selected ),
        VALUES ( Table[CustomerId] )
    )
RETURN
    IF ( reviewCouint >= 1, reviewCount-1 )

Notice: calendar is the source of slicer, please break relationships between current table and calendar table to ignore 'auto exist' filter.

Understanding DAX Auto-Exist

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you so much Sheng.

I have tried your DAX but did not work for my requirement (I may be missing something).

Herewith the DAX:

Measure =

VAR selected =

MAX ( Calendar[Date] )

VAR reviewCount =

CALCULATE (

COUNTROWS ( 'Review Date' ),

FILTER ( ALLSELECTED ( 'Review Date' ), [BusinessDate] <= selected ),

VALUES ( 'Review Date'[CustomerId] )

)

RETURN

IF ( reviewCount >= 1, reviewCount-1 )

My simple model:

Sample Data:

My Report layer:

Here in the Measure I am expecting: 2, because when I choose 06/17/2019 from the Date slicer ( compare BusinessDates between 6/16/2019 and  6/17/2019 (Date slicer)) , there are 2 ReviewDates have changed.

When I choose 06/18/2019 from the Date slicer, in the Measure I am expecting: 2, because  ( compare BusinessDates between 6/17/2019 and  6/18/2019 (Date slicer)) , there are 2 ReviewDates have changed. Here is screenshot:

I hope i explained it properly and you can understand my requirement.

Thank you

[Anonymous]

Hi @Anonymous ,

You can try to use following measure formula to find out changed records:

Measure =
VAR currDate =
    MAX ( 'Calendar'[Date] )
RETURN
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                FILTER (
                    ALLSELECTED ( T1 ),
                    [BusinessDate] <= currDate
                        && [BusinessDate] >= currDate - 1
                ),
                [CustomerId],
                "DC Review", COUNTROWS ( VALUES ( T1[ReviewDate] ) )
            ),
            [DC Review] > 1
        )
    )

Regards,

Xiaoxin Sheng