Rolling subtraction by quarter

[Mr_Indy]

I have the total number (108) and i want to subtract it with the value i have every quarter. (I can have the total number as a Column or as measure.) My problem is that I want to use the "Count by quarter", which is giving me the total amount of items that need to be subtracted. The way the report is builded, i have the same number for every record. What i want to have at the end is
Total = 108

Q4 2022 = 7
New Total = 108-7 = 101
Q1 2023 = 17
New total = 101-17= 84

etc
Any idea how can i do it with DAX or some other method?

Thank you in advance

[Anonymous · Accepted solution]

Hi @Mr_Indy ,

We need a column like [Index] or [YearQuarter] to sort your [QT] column. Here I suggest you to create a DimDate table by dax.

DimDate = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 01, 01 ), DATE ( 2023, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Quarter", QUARTER ( [Date] ),
    "QT",
        "Q" & ""
            & FORMAT ( [Date], "Q YYYY" ),
    "QY",
        YEAR ( [Date] ) * 100
            + QUARTER ( [Date] )
)

Relationship:

Then you can try this code to create a calcualted column.

Rolling subtraction by quarter =
VAR _STEP1 =
    SUMMARIZE (
        ALL ( 'Table' ),
        'Table'[Burndown],
        'Table'[Count by quarter],
        'Table'[QT],
        "QY", CALCULATE ( MAX ( DimDate[QY] ) )
    )
VAR _STEP2 =
    ADDCOLUMNS (
        _STEP1,
        "Rolling subtraction by quarter",
            108
                - SUMX ( FILTER ( _STEP1, [QY] <= EARLIER ( [QY] ) ), [Count by quarter] )
    )
RETURN
    SUMX (
        FILTER ( _STEP2, [QT] = EARLIER ( 'Table'[QT] ) ),
        [Rolling subtraction by quarter]
    )

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.

[Mr_Indy]

Thank you Rico, it worked like a charm!