Create a cumulative count based on criteria

[AragakiSora]

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!

[Anonymous · Accepted solution]

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 Team

If 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.

[amitchandak]

@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-3d98a5b0e07f

Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42