Compare between two periods and find the difference

[prabhu0505]

Hi,

Below table has two periods and for highlighted records stage changes from period 1 (Mar-15) to period 2 (
Apr-15). The difference is 5, I need to write a set analysis expression for getting this.

Catch is: I have to consider only those records, which ones EMP ID are repeated in period 2 and Stage must have changed.

Tried something like,

=sum({[Waterfall2]<[Emp ID]=P({[Waterfall1]} [Emp ID]), Stage=P({Waterfall1} [Emp ID])+P({Waterfall1} [Stage])>} Hours)

Didn't work out. Appreciate any help on this. Thanks!

Period	Emp ID	Dept	Stage	Hours
Mar-15	101	IT	1	25
Mar-15	201	CSE	1	50
Mar-15	301	IT	1	25
Mar-15	101	CSE	1	50
Mar-15	201	IT	1	75
Mar-15	301	CSE	1	45
Mar-15	101	Mechanical	1	50
Mar-15	201	Mechanical	1	25
Mar-15	301	Mechanical	1	35
Mar-15	101	Civil	1	85
Mar-15	201	Civil	1	100
Mar-15	301	Civil	1	150
Mar-15	401	ECE	2	1000
Apr-15	101	IT	1	30
Apr-15	201	CSE	1	55
Apr-15	301	IT	1	30
Apr-15	101	CSE	1	55
Apr-15	201	IT	2	80
Apr-15	301	CSE	1	50
Apr-15	101	Mechanical	1	55
Apr-15	201	Mechanical	1	30
Apr-15	301	Mechanical	1	40
Apr-15	101	Civil	1	90
Apr-15	201	Civil	1	105
Apr-15	301	Civil	1	155

[t_chetirbok]

Hello!

if i understood you correctly, you want something like this

you can use expression:

=if(

only({Waterfall1}Stage)<>only({Waterfall2}Stage)

,fabs( only({Waterfall1}Hours)-only({Waterfall2}Hours))

)

[prabhu0505 · Accepted solution]

Thanks! t.chetirbok

Below expression gave me the result.

=sum(aggr

(if(

only({Waterfall1}[Emp ID])=only({Waterfall2}[Emp ID]) and only({Waterfall1}Dept)=only({Waterfall2}Dept) and

only({Waterfall1}Stage)<only({Waterfall2}Stage)

,fabs( only({Waterfall1}Hours)-only({Waterfall2}Hours))

), [Emp ID], Dept))