Rolling subtraction by quarter

Hi Anonymous ,

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.