Calculate Difference between months

Question

Dears,

I have a fact table with Sales Values and Date dimension. Fact table contains cumulative values, for example: January: 1100, February: 1100+1000, March: 1100+1000+1200 etc.

I want to add measure which returns values for seperate months. For example: in Jan 1100, in Feb 1000, in March 1200. I was trying to use this code:

SUM('SALES'[SALES VALUE])-CALCULATE(SUM('SALES'[SALES VALUE]),PREVIOUSMONTH('DATE'[DATE])

but it still returns invalid value.

Below you find Excel with result in column D which I want to receive.

Have you got a proper Calendar/Date table setup, ie 1/9/2020 as the date not 20200901? — Ben
I also meant to say that your formula works for me, with some quick dummy data I threw together and a proper Calendar table — Ben
Can you put up a screenshot of your data model, relationships, matrix/visualisation with the incorrect result? — Ben

Sanchit Sood Sanchit Sood · Accepted Answer · 2020-04-09T20:07:35

Create a column as below :

CurrentMonthSales = 
var prev_month =
                CALCULATE(SUM(Sales[Sales Value]),FILTER(Sales,EARLIER(Sales[Date]) = NEXTMONTH(Sales[Date])))
    return
                Sales[Sales Value] - prev_month

Calculate Difference between months

1 Answers