Forum Discussion
Running total issue
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 |
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
2 Replies
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-TITHW/m-p/434008Hi 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
