Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
Regards,
Xiaoxin Sheng
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:
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |