Archive for category PowerPivot Ramblings
PowerPivot DAX Session Notes 2 – Previous Row
Posted by Gavin Russell-Rockliff in PowerPivot Ramblings on September 23, 2010
=CALCULATE(MAX(Table1[Date]), (FILTER(Table1, EARLIER(Table1[Code])=Table1[Code] && EARLIER(Table1[Date])>Table1[Date])))
Note: I’m still coming to grips with the EARLIER function, seems to work semantically the same as an INNER join… will hopefully post a better explanation when I understand some more…
2. Using similar thinking, I could then join back to [Table1] and return the Value at the point in time established in the previous step.
(I would love any feedback if it’s possible to merge this with the previous step, may be more efficient)
=CALCULATE(SUM(Table1[Value]), (FILTER(Table1, EARLIER(Table1[Code])=Table1[Code] && EARLIER(Table1[DatePrevious])=Table1[Date])))
3. Finally, Profit is then just the difference between the current value and the previous value for the CLOSE rows.
=if(Table1[Position]="CLOSE", Table1[Value]-Table1[ValuePrevious], BLANK())
Results in the table below.
PowerPivot DAX Session Notes 1 – Dates
Posted by Gavin Russell-Rockliff in PowerPivot Ramblings on September 13, 2010