Forum Discussion
Calculate difference between to rows in the same column
I am trying to calculate the variance of projects, over different periods. Periods are referenced as P1, P2,...
The user should be able to pick the periods that they want to compare and see the projects listed
| Period | Object | WIP | Index | Index.1 |
| FY22 P01 | PRJ | 28,906 | 0 | 1 |
| FY22 P02 | PRJ | 3,906 | 1 | 2 |
| FY22 P01 | PRJ2 | 5,506 | 0 | 1 |
| FY22 P02 | PRJ2 | 8,000 | 1 | 2 |
I have tried a dax formula, but I can't find one that has more than one filter.
My DAX:
Variance = VAR DIFF = WIP[ WIP]-
CALCULATE(sum(WIP[ WIP]),
FILTER('WIP', WIP[Index1]=EARLIER(WIP[Index])))
return IF(DIFF=VALUE(WIP[ WIP]), 0,
DIFF
)
Can you help?
6 Replies
Hi Anonymous,
What do you mean by "more than one filter?" Also, are you trying to get the difference between two succeeding periods for every object? If that is the case, try this formula as a calc column
Diff = VAR _prev = CALCULATE ( SUM ( Data[WIP] ), FILTER ( ALL ( Data ), Data[Index] < EARLIER ( Data[Index] ) && Data[Object] = EARLIER ( Data[Object] ) ) ) RETURN Data[WIP] - _prevPlease post your expected result.
