cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

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)

1 ACCEPTED SOLUTION
Microsoft Employee

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

16 REPLIES 16
New Member

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]),
)
RETURN
DIVIDE(
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

New Member

Need help in fixing this, kindly suggest

New Member

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]),
)
RETURN
DIVIDE(
DISTINCTCOUNT('Append1'[Merchant Id]) - __PREV_MONTH,
__PREV_MONTH
)

Anonymous
Not applicable

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?

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)
)```
New Member

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?

New Member

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

Regards,

Manish Nigam

Frequent Visitor

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
New Member

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.

Microsoft Employee

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

Helper III

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.

Helper III

What is "DATO" in this expression?

Frequent Visitor

DATO = DATE 🙂

Frequent Visitor

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

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

Frequent Visitor

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

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.

New Member

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.