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] )
Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
3 years ago

That looks like an output table, i.e. matrix visual.

What is your input data? If it looks like the table below then it's an easy setup

MonthRequestedConfirmed
Jan24002300
Feb1000800

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    hi Stachu 

     

    yes, table is as you reported. For each row I need to calculate the difference (Requested - Confirmed). 

     

    Moreover, I need that this difference is cumulative, so that each month difference is summed to the one calculated in the previous month

     

    e.g.:

    Jan = 2.400 - 2.300 = 100

    Feb = (1.000 - 800) + January = 200 + 100 = 300

    March = (xxx - xxx) + Feb = ( ... ) + 300