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)
 )
Tormod_GK's avatar
Tormod_GK
Frequent Visitor
10 years ago

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.

 

IndexDateDepartmentValueDifference
10015.02.201610050 00050 000
234515.03.201610070 00020 000
758515.04.201610070 0000
65432515.05.201610080 00010 000
34532115.06.201610020 000-60 000
  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    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.

    • fjcampos's avatar
      fjcampos
      Frequent Visitor
      6 years ago

      I have a similar problem, try this solution but it shows me the following:


      Using this formula always results in zero for me MOV_NETO_RRC
      I would like to filter from January to April and only show me the values enclosed with blue.
      My applied measure is as follows:

      MOV_NETO_RRC = SUM(RRC[RESERVA_RIESGO_CURSO]) - CALCULATE(SUM(RRC[RESERVA_RIESGO_CURSO]);PARALLELPERIOD(DIM_TIEMPO[FECHA].[Date];-1;MONTH))
       
    • ripstaur's avatar
      ripstaur
      Helper III
      5 years ago

      What is "DATO" in this expression? 

      • Tormod_GK's avatar
        Tormod_GK
        Frequent Visitor
        5 years ago

        DATO = DATE 🙂

  • SantiagoBS's avatar
    SantiagoBS
    New Member
    6 years ago

    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.