cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tormod_GK
Frequent Visitor

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?

 

DateValueDifference
15.02.201650 00050 000
15.03.201670 00020 000
15.04.201670 0000
15.05.201680 00010 000
15.06.201620 000-60 000

 

Best regards,

Tormod (Norway)

 

1 ACCEPTED SOLUTION

@Tormod_GK

 

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,

View solution in original post

13 REPLIES 13
jasbir_singh83
Regular Visitor

Hi Please see image below which I created through Quick Measure & Time Intelligence:

 

jasbir_singh83_1-1614770552580.png

 

 

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?

 

ankitpatira
Community Champion
Community Champion

@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 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,

 

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.

 

commoditymonthsquantity
114/1/2017500
115/1/2017700
116/1/20171000
117/1/20171500
124/1/2017600
125/1/2017900
126/1/20171400
127/1/20172000
134/1/2017100
135/1/2017500
136/1/2017600
137/1/2017750
144/1/20171000
145/1/20172000
146/1/20173000
147/1/20174000

 

Expected result is below:

 

commoditymonthsquantity
114/1/2017500
115/1/2017200
116/1/2017300
117/1/2017500
124/1/2017600
125/1/2017300
126/1/2017500
127/1/2017600
134/1/2017100
135/1/2017400
136/1/2017100
137/1/2017150
144/1/20171000
145/1/20171000
146/1/20171000
147/1/20171000

 

 

 Thanks in advance

 

Regards,

 

Manish Nigam

 

 

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.

 

IndexDateDepartmentValueDifference
10015.02.201610050 00050 000
234515.03.201610070 00020 000
758515.04.201610070 0000
65432515.05.201610080 00010 000
34532115.06.201610020 000-60 000

Hi @Tormod_GK 

 

I encountered almost the same problem as you, my solution was to use a calculated index based on the lookup values I needed and using the concatenate function; you would first need to generate the index as a calculated column, as follows:

 

ConcatIndex = TABLENAME[Index] & TABLENAME[Date] & TABLENAME[Department] 

 

Then, use the ConcatIndex to retrieve the value for each Department & Index and the PREVIOUSMONTH function for the previous month:

 

 

Difference = 
TABLENAME[Value] - IF(
  TABLENAME[Value] = 0,
  TABLENAME[Value],
  LOOKUPVALUE(
   TABLENAME[Value],
   TABLENAME[ConcatIndex],
   (TABLENAME[Index] & (PREVIOUSMONTH(TABLENAME[Date] & TABLENAME[Department])))
 )

 

 

I'm not quite sure what's the purpose of the conditional on the last code, it's up to you to use it or not.

 

I hope this helps to you or someone else, even though i'm 4 years late hehe.

 

@Tormod_GK

 

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,

I have data from GitHub on the COVID cases and deaths. It has daily counts for each county in the US, for every day since January 22. I would like to be able to calculate the difference between days ("new cases" or "new deaths"). I have been able to do that in Tableau, but have not figured out how to make it happen in a calculated column in M or Dax in Power BI. In other words, for a particular County in a particular State (or for a particular FIPS Code), I need to be able to subtract the count of Confirmed Cases for Jan 22 from the same count for Jan 23. Also, if the result ends up less than zero, I need to be able to have it return 0. Any ideas? I could build a concatenated index for county, state, plus an index number, if that helps. 

What is "DATO" in this expression? 

DATO = DATE 🙂

I have a similar problem, try this solution but it shows me the following:

cuadro_RRC.PNG
Using this formula always results in zero for me MOV_NETO_RRC
I would like to filter from January to April and only show me the values enclosed with blue.
My applied measure is as follows:

MOV_NETO_RRC = SUM(RRC[RESERVA_RIESGO_CURSO]) - CALCULATE(SUM(RRC[RESERVA_RIESGO_CURSO]);PARALLELPERIOD(DIM_TIEMPO[FECHA].[Date];-1;MONTH))
 

@v-sihou-msft

 

I have been trying to carry out a similar calculation, but whenever I use a DAX function, such as PREVIOUSYEAR, as a filter in CALCULATE nothing gets returned. 

 

DAStatError.PNG

My current expression is:

Difference = CALCULATE(SUM('Yearly Summary'[Sales by Status per Year]), PREVIOUSYEAR('Yearly Summary'[Date].[Date]))

 

But this returns just blanks. Any idea why this may be or ideas for trouble shooting? If I drop the filter, it works. If I try other filters that are not DAX functions such as 'Yearly Summary'[Date].[Date]=2017 it works.

I aslo got blank results after implementing PREVIOUSYEAR function same as you. However problem resolved when

1) I added Date table with all uninterrupted dates within specified period.

2) Marked it as date table with date column as key.

3) Established relation between date table and data table.

4) Use date table column in PREVIOUSYEAR function in stead of date column from data table.

It worked as expected after following steps above.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors