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.
Hi all,
Please issue sample of my data set, My data is large data below i am showing sample
Client Name | Case created date | case no | type |
a | 22/08/2019 | 222 | x |
a | 22/08/2019 | 223 | x |
a | 21/08/2019 | 224 | x |
b | 20/07/2019 | 225 | y |
b | 19/07/2019 | 226 | y |
b | 15/07/2019 | 227 | y |
b | 14/07/2019 | 228 | z |
c | 13/05/2019 | 223 | z |
c | 16/05/2019 | 229 | z |
c | 17/04/2019 | 230 | z |
c | 13/03/2019 | 231 | x |
c | 12/2/2019 | 232 | x |
I want to calcualte distinct count of client code based on cases id we received last 90 days by running total. (last three months if we received cases from the client which means client is alive)
I want to represents the data like below to create visual
Total Alive clients | |||
July | Aug | ||
X | 16 | 15 | ( cases received in last three months) |
Y | 17 | 14 | |
Z | 18 | 12 |
Solved! Go to Solution.
Hi @Anonymous ,
Please try to use following measure formula if it suitable for your requirement:
Measure = VAR curr = MAX ( 'Table'[Case created date] ) RETURN CALCULATE ( COUNTA ( 'Table'[case no] ), FILTER ( ALLSELECTED ( 'Table' ), [Case created date] >= DATE ( YEAR ( curr ), MONTH ( curr ) - 3, 1 ) && [Case created date] <= curr ), VALUES ( 'Table'[Client Name] ) )
Regards,
Xiaoxin Sheng
Hi @Anonymous ,
Please try to use following measure formula if it suitable for your requirement:
Measure = VAR curr = MAX ( 'Table'[Case created date] ) RETURN CALCULATE ( COUNTA ( 'Table'[case no] ), FILTER ( ALLSELECTED ( 'Table' ), [Case created date] >= DATE ( YEAR ( curr ), MONTH ( curr ) - 3, 1 ) && [Case created date] <= curr ), VALUES ( 'Table'[Client Name] ) )
Regards,
Xiaoxin Sheng
Have you tried using the built-in Running Total Quick Measure? Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |