Forum Discussion

planc7's avatar
planc7
Helper I
2 years ago
Solved

Cumulative Total with Variable Column

Hi all,

I'm encountering difficulties in finding the right formula to calculate Running Total on measure that comes from a virtual column.

Just to let you know, this is the formula behind this measure:

you just need to pay to attention to the last part where there is the SUMX formula

 

 

Spoiler

test =

VAR _Table =

CALCULATETABLE(

        ADDCOLUMNS(

            'PHISICAL_TABLE',

            "@Key",

                IF(NOT ISBLANK(PHISICAL_TABLE[Actual/Planned Dummy]),

                PHISICAL_TABLE[Year]+1&FORMAT(PHISICAL_TABLE[Week Dummy],"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(SELECTEDVALUE(PHISICAL_TABLE[Year])=VALUE(LEFT([@Key],4)),[@Actual/PlannedPY],BLANK())),

NOT ISBLANK([@Actual/PlannedPY2]))

 

VAR _Result = SUMX(_Table2,[@Actual/PlannedPY2])

 

RETURN

_Result

 

What I need to do, is calculate the running total for variable column (@Actual/PlannedPY2), for each year/week.

 

Any suggestion?

Thank you!

 

Here below the dataset used:

YearWeekBrandClusterKey BrandActual/PlannedYear&WeekYear+1
202351XX2024-AA202351XX2024-AA42023512024
202451XX2024-AA202451XX2024-AA 2024512025
202350XX2024-AA202350XX2024-AA42023502024
202450XX2024-AA202450XX2024-AA 2024502025
202349XX2024-AA202349XX2024-AA52023492024
202449XX2024-AA202449XX2024-AA 2024492025
202347XX2024-AA202347XX2024-AA92023472024
202447XX2024-AA202447XX2024-AA 2024472025
202345XX2024-AA202345XX2024-AA122023452024
202445XX2024-AA202445XX2024-AA 2024452025
202344XX2024-AA202344XX2024-AA132023442024
202444XX2024-AA202444XX2024-AA 2024442025
202343XX2024-AA202343XX2024-AA192023432024
202443XX2024-AA202443XX2024-AA 2024432025
202342XX2024-AA202342XX2024-AA172023422024
202442XX2024-AA202442XX2024-AA 2024422025
202341XX2024-AA202341XX2024-AA142023412024
202441XX2024-AA202441XX2024-AA 2024412025
202340XX2024-AA202340XX2024-AA72023402024
202440XX2024-AA202440XX2024-AA 2024402025
202339XX2024-AA202339XX2024-AA102023392024
202439XX2024-AA202439XX2024-AA 2024392025
202411XX2024-AA202411XX2024-AA102024112025
202511XX2024-AA202511XX2024-AA 2025112026
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

4 Replies