Running Total with categories

Hi rupeshghosh18 ,

According to your description, not all categories are available on each date, this is what is causing the problem.

Here's my sample, there is no category C in date 2/7/2022.

It display as yours. In the date 2/7/2022, there is no category C, so the Running Submissions can't calculate by category C.

Here's my solution.

1.In Power Query, right click Main Query table, then select Duplicate.

2.Select date column, then click Remove Columns>Remove Other Columns.

3.Select Remove Rows>Remove Duplicates.

4.Select Custom Column.

Write the below formula, click OK.

=#"Main Query"[Category]

5.Click the expand tab in the right upper coner of the custom column, then select Expand to New Rows.

6.Select the two columns at the same time(Ctrl+select), then click Remove Rows>Remove Duplicates.

7.Rename the custom column name, get this table.

8.Create a calculated column in new table.

Submissions = 
VAR _SUB =
    MAXX (
        FILTER (
            ALL ( 'Main Query' ),
            'Main Query'[date] = EARLIER ( 'Main Query (2)'[date] )
                && 'Main Query'[Category] = EARLIER ( 'Main Query (2)'[Category] )
        ),
        'Main Query'[Submissions]
    )
RETURN
    IF ( _SUB <> BLANK (), _SUB, 0 )

9.Create a measure

Running Submissions2 = 
CALCULATE (
    SUM ( 'Main Query (2)'[Submissions] ),
    FILTER (
        ALLSELECTED ( 'Main Query (2)'[date] ),
        'Main Query (2)'[date] <= MAX ( 'Main Query (2)'[date] )
    )
)

10.Get the expected result.

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.