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,

SN1703's avatar
SN1703
New Member
3 years ago

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
    )