Forum Discussion
Calculate difference from previous month
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,
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,
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.
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.
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?
