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 "Sum of Confirmed orders".

Each sum is the amount of orders requested or confirmed in each month.

 

I would like to add another column, for each month, that calculates the difference between the two columns. This additional column however should also be comulative and sum the diffences for the whole year.

 

Data example:

JanuaryFebruaryMarchApril
RequestedConfirmed**bleep** DifferenceRequestedConfirmed**bleep** DifferenceRequestedConfirmed**bleep** DifferenceRequestedConfirmed**bleep**. Difference
2400230010010008003001900175045020001500950

 

I arrive to calculate the Difference in each month, but struggle to create the cumulative difference

 

any suggestion from the guru of DAX? 

🙂

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] )

4 Replies

  • tamerj1's avatar
    tamerj1
    Community 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

  • Stachu's avatar
    Stachu
    Community 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