Running total chart that does not reset for each calendar year

[brizzdizz]

Hello, I have a line & clustered column chart that has a running total measure applied to it. The running total resets back to 0 at the beginning of each year. I'm trying to get the running total to not reset and calculate the running total for the entirety of the data but am not having any luck.

In the screenshot you can see where my running total drops from 308 in 2021 December to 9 in 2022 January. I'd like 2022 January to be 317.

This is sample data where I expect 2020 December to be 2 and 2021 January to be 6:

PO Number	Revision	Issue Date
AR15733	1	15-Dec-20
BR63777	1	10-Dec-20
CH37333	3	5-Jan-21
JW5256	0	13-Jan-21
RB6622	3	6-Jan-21
RB6622	4	22-Jan-21

This is the measure I'm currently using:

Cumulative PO Revisions =

CALCULATE(

    COUNTA('q50183 PO Commitment by Revision'[Revision]),

    FILTER(

        CALCULATETABLE(

            SUMMARIZE(

                'q50183 PO Commitment by Revision',

                'q50183 PO Commitment by Revision'[Issue Date].[MonthNo],

                'q50183 PO Commitment by Revision'[Issue Date].[Month]

            ),

            ALLSELECTED('q50183 PO Commitment by Revision')

        ),

        ISONORAFTER(

            'q50183 PO Commitment by Revision'[Issue Date].[MonthNo], MAX('q50183 PO Commitment by Revision'[Issue Date].[MonthNo]), DESC,

            'q50183 PO Commitment by Revision'[Issue Date].[Month], MAX('q50183 PO Commitment by Revision'[Issue Date].[Month]), DESC

        )

    )

)

[v-rzhou-msft · Accepted solution]

Hi @brizzdizz ,

I suggest you to try to create a DimDate table and then create measures to count revision and cumulative revision.

DimDate = 
ADDCOLUMNS (
    CALENDAR (
        EOMONTH ( MIN ( 'q50183 PO Commitment by Revision'[Issue Date] ), -1 ) + 1,
        EOMONTH ( MAX ( 'q50183 PO Commitment by Revision'[Issue Date] ), 0 )
    ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "MMMM" )
)

Relationship:

Measures:

Count PO Revisions = 
COUNT ( 'q50183 PO Commitment by Revision'[Revision] )

Cumulative PO Revisions = 
SUMX (
    FILTER ( ALL ( DimDate ), DimDate[Date] <= MAX ( DimDate[Date] ) ),
    [Count PO Revisions]
)

Result is as below.

Best Regards,
Rico Zhou

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

[lbendlin]

Shouldn't January 2021 be 12?