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