Cumulative Total with Variable Column

Hi planc7 ,

I create a table as you mentioned.

Then I do some changes in your DAX codes. I delete SELECTEDVALUE function and here is the DAX code.

test =
VAR _Table =
    CALCULATETABLE (
        ADDCOLUMNS (
            'PHISICAL_TABLE',
            "@Key",
                IF (
                    NOT ISBLANK ( PHISICAL_TABLE[Actual/Planned] ),
                    PHISICAL_TABLE[Year] + 1
                        & FORMAT ( PHISICAL_TABLE[Week], "00" ) & 'PHISICAL_TABLE'[Brand] & 'PHISICAL_TABLE'[Cluster],
                    PHISICAL_TABLE[Key Brand]
                )
        ),
        REMOVEFILTERS ( PHISICAL_TABLE[Year], PHISICAL_TABLE[Year&Week] )
    )
VAR _Table1 =
    FILTER (
        ADDCOLUMNS (
            ADDCOLUMNS (
                _Table,
                "@Actual/PlannedPY",
                    IF (
                        ISBLANK ( PHISICAL_TABLE[Actual/Planned] ),
                        CALCULATE (
                            MAX ( PHISICAL_TABLE[Actual/Planned] ),
                            FILTER ( _Table, [@Key] = EARLIER ( [@Key] ) )
                        )
                    )
            ),
            "@Actual/PlannedPY2",
                IF (
                    PHISICAL_TABLE[Year] = VALUE ( LEFT ( [@Key], 4 ) ),
                    [@Actual/PlannedPY],
                    BLANK ()
                )
        ),
        NOT ISBLANK ( [@Actual/PlannedPY2] )
    )
RETURN
    SUMX ( _Table1, [@Actual/PlannedPY2] )

Finally you will see what you want.

Best Regards

Yilong Zhou

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