Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Cumulate difference between each months

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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.