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

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'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.