Cumulate difference between each months

[Anonymous]

Trying to write a measure to calculate the cumulative difference between months.

For example, I'm working with the data below.

Yearmonth     Sales

2020/01/01    10

2020/02/01    15

2020/03/01    14

2020/04/01    11

Total               50

Then each month difference is 5, -1, -3 and cumulative total difference is 1 (5+ -1 + -2).

Can you please help me to figure out how to get the total (i.e. 1)?

Thanks!

[Anonymous]

Hi @Anonymous 

You can try some thing similar to below:

Previous Month Sales = CALCULATE(sum('Table'[Sales]),ALL('Table'),PREVIOUSMONTH('Table'[Yearmonth ]))

Sales Diff = IF(ISBLANK([Previous Month Sales]),BLANK(),sum('Table'[Sales]) - [Previous Month Sales]) 

Thanks

Manoj

[Anonymous]

Thank you for the help! I created two measures and it gives me a difference for each month. However, I can't seem to add the difference to get total 1.

Below is a bar chart using the measure.

I would like a cumulated difference bar chart. For example, if I select 04/01/2020, I would like the bar chart to show 1. But it's showing -3, just a difference between March and April.

Can you please help?

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file for you(see attachment) base on your requirement, please check whether that is what you want.

Difference = 
var _cursales=MAX('Table'[Sales])
var _curdate=MAX('Table'[Yearmonth])
var _predate=CALCULATE(MAX('Table'[Yearmonth]),FILTER(ALL('Table'),'Table'[Yearmonth]<_curdate))
var _presales=CALCULATE(MAX('Table'[Sales]),FILTER(ALL('Table'),'Table'[Yearmonth]=_predate))
return 
if(ISBLANK(_presales),BLANK(),_cursales-_presales)

Culmulative difference = SUMX(FILTER(ALL('Table'),'Table'[Yearmonth]<=SELECTEDVALUE('Table'[Yearmonth])),[Difference])

Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.