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    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!

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.

3 Replies

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