Forum Discussion

edayeh's avatar
edayeh
New Member
5 years ago
Solved

Calculate Snapshot Data Delta -

Hello

 

I am trying to get deltas from snapshotted data and I can't seem to figure out how. 

I snapshotdata (in SFDC) daily by Account and ARR for that account and bring store that value. I would love to be able to calculate the delta of any daily changes for a specific account. Below is what I would love to get.

I want to create a colum (Delta below) that will show me what accounts changed from the previous date. Small sample size below. 

 

NameARRDateDelta
Account 1 $    68,22329-Oct 
Account 1 $    26,65630-Oct($41,567)
Account 2 $    68,16229-Oct 
Account 2 $    68,16230-Oct$0
Account 3 $  177,32429-Oct 
Account 3 $      1,67930-Oct($175,645)
Account 4 $    12,81929-Oct 
Account 4 $      8,07130-Oct($4,748)
Account 5 $          18029-Oct 
Account 5 $          18030-Oct$0

I have over 500 accounts so the snapshot have over 500 records daily. 

 

How can I get a formula to show me this? thanks in advance

Need Help
  • camargos88's avatar
    camargos88
    5 years ago

    edayeh ,

     

    You can use this code to create a calculated column:

    Delta = 
VAR _date = CALCULATE(MAX('Table'[Date]), FILTER('Table', 'Table'[Date] < EARLIER('Table'[Date])))
RETURN IF(_date <> BLANK(), CALCULATE(SUM('Table'[ARR]), FILTER('Table', 'Table'[Name] = EARLIER('Table'[Name]) && [Date] = _date)) - 'Table'[ARR], BLANK())

     

2 Replies

  • camargos88's avatar
    camargos88
    Community Champion
    5 years ago

    edayeh ,

     

    You can use this code to create a calculated column:

    Delta = 
VAR _date = CALCULATE(MAX('Table'[Date]), FILTER('Table', 'Table'[Date] < EARLIER('Table'[Date])))
RETURN IF(_date <> BLANK(), CALCULATE(SUM('Table'[ARR]), FILTER('Table', 'Table'[Name] = EARLIER('Table'[Name]) && [Date] = _date)) - 'Table'[ARR], BLANK())