Calculate difference between to rows in the same column

[Anonymous]

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?

[danextian]

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] - _prev

[Anonymous]

Thank you for this.

Sorry but it didn't work.

I'm not sure what is doing.

An object where variance should be 0, shows different figures

[danextian]

Please post your expected result.

[Anonymous]

Period	Object	WIP	Index	Index.1	Variance
FY22 P01	PRJ	500	0	1	0
FY22 P01	PRJ2	550	0	1	0
FY22 P02	PRJ	600	1	2	50
FY22 P02	PRJ	650	1	2	50
FY22 P03	PRJ	700	2	3	50
FY22 P03	PRJ2	800	2	3	100

[danextian]

Hi @Anonymous ,

Given that there are repeating indices in your data, what is your logic in calculating the variance in the highlighted cells below? What column can be used that will make each row unique  and will indicate that a  row comes before the other?

[Anonymous]

Thank you so much,

There was an error in that table. 

Can you look at this one:

Period	Object	WIP	Index	Index.1	Variance
FY22 P01	PRJ	500	0	1	0
FY22 P02	PRJ	600	1	2	100
FY22 P03	PRJ	650	2	3	50
FY22 P01	PRJ2	700	0	1	0
FY22 P02	PRJ2	550	1	2	(150)
FY22 P03	PRJ2	800	2	3	250