Change of value from second last date

[shane7mcdonald]

Hi all, I'm trying to figure out how to make a measure that can give the value of the last date minus (-) the value of the second last date from each separate Id in a column. So, on any given day I should be able to tell from each item in a coumn what the change was from the last recorded date. Basically I'm after the variance.

Would love to hear any ideas! 

[tex628 · Accepted solution]

Create 2 calculated columns, 

Latest date = 
Calculate(
MAX([Date]),
[ID]=EARLIER([ID]
)

Latest date -1 = 
Calculate(
MAX([Date]),
[ID]=EARLIER([ID],
[Date] <> [Latest Date]
)

Should give you the 2 dates you are looking for, then you can simply use them in a measure calculation,

Latest date amount = 
Calculate( Sum([amount]) , [Date] = selectedvalue([Latest date])

Latest date -1 amount = 
Calculate( Sum([amount]) , [Date] = selectedvalue([Latest date -1])

and finally

Difference = [Latest date amount] - [Latest date amount]