Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Subtract Values Row by Row with EARLIER function

Dear Contributors, 

 

Please help me to adjust the Calculated Column formula, so that the outcome was the same as the Measure.

Note, that for Calculated Column a Base value (Resources Needs) is not summarized by date, opposed to the Measure.

 

Difference Column = 'Resources Needs'[Resources Needs]-CALCULATE(MAX('Resources Needs'[Resources Needs]);FILTER('Resources Needs';'Resources Needs'[Project]=EARLIER('Resources Needs'[Project])&&'Resources Needs'[Position]=EARLIER('Resources Needs'[Position])&&'Resources Needs'[Recruitment Status]=EARLIER('Resources Needs'[Recruitment Status])&&'Resources Needs'[Date]<EARLIER('Resources Needs'[Date])))

 

 

 

Difference Measure = SUM('Resources Needs'[Resources Needs])-CALCULATE(SUM('Resources Needs'[Resources Needs]);PARALLELPERIOD('Resources Needs'[Date];-1;MONTH))

 

Pbix file is available here

 

Best regards,

Anna

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    I doubt that MIN, as well as MAX is correct for my case:

     

     

    Basically I need (Sum [Resources Needs] within a Month)  - (Sum [Resources Needs] within a Previous Month), but in this case how to deal with the result:

      

  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    I guess where I was going with that is that you appear to have multiple matching rows so you either need to have better filters or you are going to need a different aggregation like AVERAGE or something.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    What happens if you substitute MIN for MAX in your column formula?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I doubt that MIN, as well as MAX is correct for my case:

       

       

      Basically I need (Sum [Resources Needs] within a Month)  - (Sum [Resources Needs] within a Previous Month), but in this case how to deal with the result:

        

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        8 years ago

        I guess where I was going with that is that you appear to have multiple matching rows so you either need to have better filters or you are going to need a different aggregation like AVERAGE or something.