Tabular Query to Compare Measures Across Dates

1
votes

I've got a number of rows and I want to calculate the difference per date.

So say I have the following:

[Date]       [Transaction Number]    [Value]

1 Jan 16  1                                     1000

2 Jan 16  1                                     980

I then want a fact that for every row will compare the value with the measure from the previous date.

So If I have a measure on SUM(Value) for the current date, I basically want SUM(CurrentDate) - SUM(PreviousDate) to see the movement.

A couple of things to note:

  1. There will actually be a couple of comparisons: previous date, previous month end, previous year end.
  2. I want this as a calculated measure not column so that I do not need to filter on the transaction number in the previous period.

What I've tried but it just comes up empty:

Previous Value :=CALCULATE(SUM(Table[Value])) - CALCULATE(SUM(Table[Value]), FILTER(Table, Table[Date] = PreviousDay(Table[Date])))
1
sqldaxssas-tabular

1 Answers

1
votes

Unfortunately I cannot tell why your measure didn't work, but following should:

Previous Value := CALCULATE(SUM(Table[Value]) - CALCULATE(SUM(Table[Value]), PREVIOUSDAY(Table[date])))