Get total sum per month from cumulative value

[Anonymous]

Hi everyone,

I've been struggeling with something. I want a calculated column that calculates the sales of that month based on the cumulitive sales.

ID      Month Year   CumAmount      Sales

1484   2019-jan      120000               -

1484   2019-feb      130000               10000

1484   2019-mar     135000               5000

Unfortunately I don't have a datevalue in the same row, but I do have "2019-jan", "2019-feb" etc. that I have connected with the same data format in a calendar table.

Can someone help me out?

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

For your requirement, please create an index column in Query Editor firstly and then create the calculated column with the formula below.

Column =
VAR a =
    CALCULATE (
        MAX ( 'Table1'[CumAmount] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[Index] = EARLIER ( Table1[Index] ) - 1 )
    )
RETURN
    IF ( ISBLANK ( a ), BLANK (), 'Table1'[CumAmount] - a )

Here is the output.

You also could have a reference of my attachement.

Best Regards,

Cherry

[aer98]

Hello,

I have the same problem but the other way round...

I have the sales amount per month and would like to show on graph the cumulative.

For exemple :

Feb=Jan+Feb

March=Jan+Feb+March

... and so on.

Could you please help me ?

Thanks,

[Anonymous]

Hi,

You can create a calculated column for that;

Running Total COLUMN =
CALCULATE (
    SUM ( 'TableName'[Value] ),
    ALL ( 'Tablename' ),
    'TableName'[Date] <= EARLIER ( 'TableName'[Date] )
)