Calculate Snapshot Data Delta -

[edayeh]

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. 

Name	ARR	Date	Delta
Account 1	$    68,223	29-Oct
Account 1	$    26,656	30-Oct	($41,567)
Account 2	$    68,162	29-Oct
Account 2	$    68,162	30-Oct	$0
Account 3	$  177,324	29-Oct
Account 3	$      1,679	30-Oct	($175,645)
Account 4	$    12,819	29-Oct
Account 4	$      8,071	30-Oct	($4,748)
Account 5	$          180	29-Oct
Account 5	$          180	30-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

[camargos88 · Accepted solution]

@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())

[edayeh]

THANK YOU SO MUCH.....YES