Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Cumulative sum in a table

Hello all,   I'm struggling to calculate a cumulative difference between two columns.    My dataset is a table that, for each month of the year, reports two values: "Sum of Requested orders" and ...
cumulative
subtotal
sum
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 
    Please try

    Difference =
VAR CurrentMonth =
    MAX ( 'Date'[Month Number] )
VAR T1 =
    CALCULATETABLE (
        VALUES ( 'Date'[Month] ),
        'Date'[Month Number] <= CurrentMonth
    )
VAR T2 =
    ADDCOLUMNS (
        T1,
        "@Difference",
            VAR CurrentMonth1 = 'Date'[Month]
            RETURN
                CALCULATE (
                    [Requested] - [Confirmed],
                    ALL ( 'Date'[Month] ),
                    'Date'[Month] = CurrentMonth1
                )
    )
RETURN
    SUMX ( T2, [@Difference] )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Anonymous 
Please try

Difference =
VAR CurrentMonth =
    MAX ( 'Date'[Month Number] )
VAR T1 =
    CALCULATETABLE (
        VALUES ( 'Date'[Month] ),
        'Date'[Month Number] <= CurrentMonth
    )
VAR T2 =
    ADDCOLUMNS (
        T1,
        "@Difference",
            VAR CurrentMonth1 = 'Date'[Month]
            RETURN
                CALCULATE (
                    [Requested] - [Confirmed],
                    ALL ( 'Date'[Month] ),
                    'Date'[Month] = CurrentMonth1
                )
    )
RETURN
    SUMX ( T2, [@Difference] )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    thanks tamerj1 it works perfectly