Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |