Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Total difference from previous date

Hi all,

 

I have 2 columns, one with values and the other with dates. The dates are non continous , for e.g. 10/10/2019, 12/10/2019, 20/10/2019 etc

 

I need to be able to get the difference between the sum of all the values from each date and previous date.

 

Is there a way to accomplish this?

 

I already have a column with the previous date gotten with this formula: 

Predate = CALCULATE (
    MAX (WeeklyOutstanding[Week] ), FILTER ( WeeklyOutstanding, WeeklyOutstanding[Week] < EARLIER ( WeeklyOutstanding[Week] ) ) )

 

Help would be greatly appreciated 

Need Help
Tips and Tricks
  • richbenmintz's avatar
    richbenmintz
    5 years ago

    Hi Anonymous,

     

    You can try measure like the following

     

     

    Delta From Previous = 
    var prev_date = CALCULATE(MAX('WeeklyOutstanding'[Week]), FILTER(all('WeeklyOutstanding'), 'WeeklyOutstanding'[Week] < SELECTEDVALUE('WeeklyOutstanding'[Week])))
    return
    sum('WeeklyOutstanding'[amount]) - CALCULATE(SUM('WeeklyOutstanding'[amount]), FILTER(ALL('WeeklyOutstanding'), 'WeeklyOutstanding'[Week] = prev_date))

     

     

    Hope that Helps

    Richard

3 Replies

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    5 years ago

    Hi Anonymous,

     

    You can try measure like the following

     

     

    Delta From Previous = 
    var prev_date = CALCULATE(MAX('WeeklyOutstanding'[Week]), FILTER(all('WeeklyOutstanding'), 'WeeklyOutstanding'[Week] < SELECTEDVALUE('WeeklyOutstanding'[Week])))
    return
    sum('WeeklyOutstanding'[amount]) - CALCULATE(SUM('WeeklyOutstanding'[amount]), FILTER(ALL('WeeklyOutstanding'), 'WeeklyOutstanding'[Week] = prev_date))

     

     

    Hope that Helps

    Richard

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    This worked great! Thank you!

     

    The  only issue now is that somehow it only works with the full data, whenever I use filters its gives the wrong amounts. Do you know why?

    • richbenmintz's avatar
      richbenmintz
      Resident Rockstar
      5 years ago

      It is removing the filter context with

      all('WeeklyOutstanding'[Week])

      , you may need to change that be just 

      ALL('WeeklyOutstanding')