Calculate difference from previous month

[Tormod_GK]

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)

[SN1703]

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)

    )

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  

[SN1703]

Need help in fixing this, kindly suggest 

[SN1703]

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)

    )

RETURN

    DIVIDE(

        DISTINCTCOUNT('Append1'[Merchant Id]) - __PREV_MONTH,

        __PREV_MONTH

    )

[Anonymous]

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?

[ankitpatira]

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

[SantiagoBS]

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?

[mnigam]

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

[Tormod_GK]

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

[SantiagoBS]

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.

[v-sihou-msft · 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,

[ripstaur]

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. 

[ripstaur]

What is "DATO" in this expression? 

[Tormod_GK]

DATO = DATE 🙂

[fjcampos]

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

[rpul]

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

[jpatil]

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.