Forum Discussion
Create a cumulative count based on criteria
Hello!
I have a table similar to this:
| Name | ID | Last Update |
| Michelle | 7/2/2023 | |
| Michelle | 1000012 | 7/5/2023 |
| Michelle | 1000012 | 8/29/2023 |
| Glory | 1000014 | 7/2/2023 |
| Glory | 7/4/2023 | |
| Glory | 8/13/2023 | |
| Glory | 1000014 | 8/18/2023 |
| Glory | 1000014 | 8/29/2023 |
| Nick | 1000101 | 7/20/2023 |
| Nick | 1000101 | 8/10/2023 |
| Nick | 1000101 | 8/13/2023 |
| Nick | 1000101 | 8/18/2023 |
| Nick | 8/29/2023 | |
| John | 1000104 | 7/3/2023 |
| John | 1000104 | 8/5/2023 |
| John | 8/29/2023 |
I am trying to create a cumulative count of missing IDs, but the hard part is that the cumulative count only considers the cases that have not been solved. For example, Michelle's ID was missing on July 2nd but the issue was resolved on the 5th, so the cumulative count should ignore Michelle's case. Glory's ID was missing on July 4th and August 13th, but the issue was resolved on the 18th, so Glory's case would not be included in the cumulative count.
I am also wondering whether it is possible to create a calculated column that marks all the unresolved missing ID cases or cases that haven't been resolved a week after their first appearances so that I can put those cases into a table.
Thank you in advance!
Hi AragakiSora ,
Please check that this calculated column meets your requirements:Flag = VAR CurrentName = 'Table'[Name] VAR CurrentDate = 'Table'[Last Update] VAR CurrentID = 'Table'[ID] VAR MinDateWithID = CALCULATE( MIN('Table'[Last Update]), FILTER( 'Table', 'Table'[Name] = CurrentName && NOT(ISBLANK('Table'[ID])) && 'Table'[Last Update] <= CurrentDate ) ) RETURN IF( ISBLANK(CurrentID) && MinDateWithID > DATEADD('Table'[Last Update], -7, DAY), 1, 0 )Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
3 Replies
- amitchandak
Super User
AragakiSora , with help from date table, create measure like
M1 = calculate(countrows(filter(Table, isblank(Table[ID]))),filter(all('Date'),'Date'[date] <=max('Date'[date])))
M2 = calculate(countrows(Table),filter(all('Date'),'Date'[date] <=max('Date'[date])))Blank only count = Countx(values(Table[Name]), if([M1] = [M2], [Name], Blank()))
Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07fRunning Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42 Hi AragakiSora ,
Please check that this calculated column meets your requirements:Flag = VAR CurrentName = 'Table'[Name] VAR CurrentDate = 'Table'[Last Update] VAR CurrentID = 'Table'[ID] VAR MinDateWithID = CALCULATE( MIN('Table'[Last Update]), FILTER( 'Table', 'Table'[Name] = CurrentName && NOT(ISBLANK('Table'[ID])) && 'Table'[Last Update] <= CurrentDate ) ) RETURN IF( ISBLANK(CurrentID) && MinDateWithID > DATEADD('Table'[Last Update], -7, DAY), 1, 0 )Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
- Ashish_Mathur
Hi,
Clearly show the exact result you are expecting.
