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,

rpul's avatar
rpul
Frequent Visitor
8 years ago

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

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.