Forum Discussion
Calculate difference from previous month
Hi.
I have a table where I need to subtract two values which are from different rows. I want the difference between the value for the current date in the row and from the row which has the previous month. This is an example where I have the columns Date and Value in my table, but I need to create the column 'Difference'.
I have also tried this DAX-formula, but the value only shows up in one date so it doesn't work.
sum(DW_Data_Salg[Inntekt])-CALCULATE(SUM(DW_Data_Salg[Inntekt]);DATEADD(DW_Data_Salg[DATO].[Date];-1;MONTH))
Is there someone who know how to do this?
| Date | Value | Difference |
| 15.02.2016 | 50 000 | 50 000 |
| 15.03.2016 | 70 000 | 20 000 |
| 15.04.2016 | 70 000 | 0 |
| 15.05.2016 | 80 000 | 10 000 |
| 15.06.2016 | 20 000 | -60 000 |
Best regards,
Tormod (Norway)
In this scenario, since you need to get the previous month data based on current slicing date, it's better to create a measure instead of a calculated column. Otherwise, you have to lookup previous row based on index column as ankitpatira suggested. Just create a measure like:
difference= sum(DW_Data_Salg[Inntekt])-CALCULATE(SUM(DW_Data_Salg[Inntekt]),PARALLELPERIOD(DW_Data_Salg[DATO].[Date],-1,MONTH))
Or
difference= sum(DW_Data_Salg[Inntekt])-CALCULATE(SUM(DW_Data_Salg[Inntekt]),PREVIOUSMONTH(DW_Data_Salg[DATO].[Date]))
Regards,
16 Replies
Tormod_GK First goto query editor in power bi desktop and under Add column, add index column from zero. Then under modelling tab create new column using below code.
Difference = TABLENAME[Value] - IF( TABLENAME[Value] = 0, TABLENAME[Value], LOOKUPVALUE( TABLENAME[Value], TABLENAME[Index], TABLENAME[Index]-1) )
Hi and thanx for your solution, but there is one problem. My table consists of 2 mill rows and 40 columns and the reference is department. So the lookup is on department and for the previous month.
Index Date Department Value Difference 100 15.02.2016 100 50 000 50 000 2345 15.03.2016 100 70 000 20 000 7585 15.04.2016 100 70 000 0 654325 15.05.2016 100 80 000 10 000 345321 15.06.2016 100 20 000 -60 000 In this scenario, since you need to get the previous month data based on current slicing date, it's better to create a measure instead of a calculated column. Otherwise, you have to lookup previous row based on index column as ankitpatira suggested. Just create a measure like:
difference= sum(DW_Data_Salg[Inntekt])-CALCULATE(SUM(DW_Data_Salg[Inntekt]),PARALLELPERIOD(DW_Data_Salg[DATO].[Date],-1,MONTH))
Or
difference= sum(DW_Data_Salg[Inntekt])-CALCULATE(SUM(DW_Data_Salg[Inntekt]),PREVIOUSMONTH(DW_Data_Salg[DATO].[Date]))
Regards,
Hi,
I have table with column names Commodity, Months and Quantity. I want to calculate the difference between month's quantity w.r t. commodity column.
can anyone help me to write DAX query.
commodity months quantity 11 4/1/2017 500 11 5/1/2017 700 11 6/1/2017 1000 11 7/1/2017 1500 12 4/1/2017 600 12 5/1/2017 900 12 6/1/2017 1400 12 7/1/2017 2000 13 4/1/2017 100 13 5/1/2017 500 13 6/1/2017 600 13 7/1/2017 750 14 4/1/2017 1000 14 5/1/2017 2000 14 6/1/2017 3000 14 7/1/2017 4000 Expected result is below:
commodity months quantity 11 4/1/2017 500 11 5/1/2017 200 11 6/1/2017 300 11 7/1/2017 500 12 4/1/2017 600 12 5/1/2017 300 12 6/1/2017 500 12 7/1/2017 600 13 4/1/2017 100 13 5/1/2017 400 13 6/1/2017 100 13 7/1/2017 150 14 4/1/2017 1000 14 5/1/2017 1000 14 6/1/2017 1000 14 7/1/2017 1000 Thanks in advance
Regards,
Manish Nigam
Hi there ankitpatira
Your DAX code finally lead me to the solution I needed, thanks a lot, however, may I ask why the conditional before looking up the value?
Hi Please see image below which I created through Quick Measure & Time Intelligence:
So my dataset is from Sep 30, 2020 to Feb 28, 2021. I calculated the %diff MoM, How can I hide March row since we do not have March data as of now and how to remove the Total column from waterfall chart created by this table?
Also is there a way to reflect the Total column as a % change of difference from Sep 30 2020 and Feb 28 2021 data? Whether there has been overall decrease or increase in Feb 2021 from starting point Sep 2020? How to have this formula as a separate measure in itself which keeps on updating when March and future months are added with base point being Sept 30, 2020?
I have tried DAX impression to calcualte %difference over previous month, however the result gives me blank value for the months not required , ex, in screenshot I am also getting blank values for Dec over Nov which is not required , also it shows me Feb blank values as well. I only need the %diff for Dec & Jan . Formula used -
MID MoM% =VAR __PREV_MONTH =CALCULATE(DISTINCTCOUNT('Append1'[Merchant Id]),DATEADD('Date'[Date],-1, MONTH))RETURNDIVIDE(DISTINCTCOUNT('Append1'[Merchant Id]) - __PREV_MONTH,__PREV_MONTH)Need help in fixing this, kindly suggest
Hi, could anyone please guide me as to how to get %difference of previous month ? I am getting blanks for months not required in the report , have to hide these blank columns , very taxing .
I am using the following DAX impression -
MID MoM% =VAR __PREV_MONTH =CALCULATE(DISTINCTCOUNT('Append1'[Merchant Id]),DATEADD('Date'[Date],-1, MONTH))RETURNDIVIDE(DISTINCTCOUNT('Append1'[Merchant Id]) - __PREV_MONTH,__PREV_MONTH)Also attaching the screenshot , would appreciate if someone can help me on this please, I only require previous
