Forum Discussion

Tormod_GK's avatar
Tormod_GK
Frequent Visitor
10 years ago
Solved

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 previo...
  • v-sihou-msft's avatar
    v-sihou-msft
    10 years ago

    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,

ankitpatira's avatar
ankitpatira
Community Champion
10 years ago

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's avatar
SantiagoBS
New Member
6 years ago

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?