Difference between rows with different categories

[omarevp]

Hello guys,this time I need you again.

This is my sample data:

Date	Unit	Extracted	Workinghours
3/1/2019 7:14	P-19	274	590025.798
3/1/2019 8:05	BD15	384	296.7
3/1/2019 8:27	APV25	26.7	775.313889
3/1/2019 8:30	P-20	365	1975.89889
3/1/2019 9:57	APV29	72	1558.49778
3/1/2019 10:11	APV27	93	576.25
3/4/2019 10:57	P-20	61	1982.22111
3/4/2019 11:11	P-20	113	1982.30833
3/4/2019 11:20	TP05	98	-348201
3/4/2019 12:51	APV25	36.1	778.898889
3/4/2019 14:16	APV27	462	594.3
3/4/2019 14:35	APV29	53	1564.17278
3/5/2019 13:21	P-19	290	590025.798
3/5/2019 14:03	P-20	103	1992.285
3/5/2019 14:19	BD15	125	314.65
3/5/2019 14:33	APV25	49.2	784.253889
3/5/2019 14:42	APV25	68	784.270556
3/5/2019 16:00	APV29	404	1568.31722
3/5/2019 16:35	RE01	478	301.401869
3/6/2019 9:56	P-19	277	590025.798
3/6/2019 13:16	BD15	110	322.75
3/6/2019 14:08	APV27	163	604.8
3/7/2019 9:44	P-20	103	2005.85944
3/7/2019 9:54	DCP01	221	99.65
3/7/2019 10:26	APV27	127	619.776389
3/7/2019 10:37	APV29	110	1581.94833
3/8/2019 9:30	P-20	204	2014.965

I need a Calculated Column that measures the difference between Workinghours in one date, and Workinghours the date before, this should be, considering the "Unit" field. Ex:

Date	Unit	Extracted	Workinghours	DIFF (desired)
3/1/2019 10:11	APV27	93	576.25
3/4/2019 14:16	APV27	462	594.3	18.05
3/6/2019 14:08	APV27	163	604.8	10.5
3/7/2019 10:26	APV27	127	619.776389	14.9763889
3/11/2019 10:14	APV27	137	627.305556	7.52916667
3/15/2019 10:32	APV27	295	648.612222	21.3066667
3/18/2019 10:34	APV27	492	663.075	14.4627778
3/22/2019 9:56	APV27	203	7005	6341.925

I need the same calculation for every unit in the same column.

Thanks in advance! All of your help would be really appreciate!

Omar.

[danno · Accepted solution]

Hi, You could do this in one measure, but here are 2 steps to get a result: 

Previous Working Hours =

IF(HASONEVALUE(Data[Unit]),

VAR CurrentUnit = SELECTEDVALUE(Data[Unit])

VAR CurrentDate = SELECTEDVALUE(Data[Date])

VAR PreviousDate =

    CALCULATE (

        MAX ( Data[Date] ),

        FILTER ( ALL(Data), Data[Unit] = CurrentUnit && Data[Date] < CurrentDate)

    )

RETURN

LOOKUPVALUE(Data[Workinghours], Data[Date], PreviousDate)

,BLANK())

Diff = IF(ISBLANK([Previous Working Hours]), BLANK(), SELECTEDVALUE(Data[Workinghours]) - [Previous Working Hours])

and here are the test results 

Date	Unit	Extracted	Workinghours	Previous Working Hours	Diff
03/01/2019 08:27	APV25	26.7	775.313889
03/04/2019 12:51	APV25	36.1	778.898889	775.313889	3.585
03/05/2019 14:33	APV25	49.2	784.253889	778.898889	5.355
03/05/2019 14:42	APV25	68	784.270556	784.253889	0.016667
03/01/2019 10:11	APV27	93	576.25
03/04/2019 14:16	APV27	462	594.3	576.25	18.05
03/06/2019 14:08	APV27	163	604.8	594.3	10.5
03/07/2019 10:26	APV27	127	619.776389	604.8	14.976389
03/01/2019 09:57	APV29	72	1558.49778
03/04/2019 14:35	APV29	53	1564.17278	1558.49778	5.675
03/05/2019 16:00	APV29	404	1568.31722	1564.17278	4.14444
03/07/2019 10:37	APV29	110	1581.94833	1568.31722	13.63111
03/01/2019 08:05	BD15	384	296.7
03/05/2019 14:19	BD15	125	314.65	296.7	17.95
03/06/2019 13:16	BD15	110	322.75	314.65	8.1
03/07/2019 09:54	DCP01	221	99.65
03/01/2019 07:14	P-19	274	590025.798
03/05/2019 13:21	P-19	290	590025.798	590025.798	0
03/06/2019 09:56	P-19	277	590025.798	590025.798	0
03/01/2019 08:30	P-20	365	1975.89889
03/04/2019 10:57	P-20	61	1982.22111	1975.89889	6.32222
03/04/2019 11:11	P-20	113	1982.30833	1982.22111	0.08722
03/05/2019 14:03	P-20	103	1992.285	1982.30833	9.97667
03/07/2019 09:44	P-20	103	2005.85944	1992.285	13.57444
03/08/2019 09:30	P-20	204	2014.965	2005.85944	9.10556
03/05/2019 16:35	RE01	478	301.401869
03/04/2019 11:20	TP05	98	-348201

[omarevp]

@dannoThanks! I think it's really useful and it's working good..

There's something missing that I forgot, sometimes the workinghours might be a 0 value, so, the measure should take the previous value different than 0, Ex:

date                   workinghours    previous workinghours

12/03/2019              110                        

13/03/2019               0                             110

14/03/2019              210                          110

15/03/2019              140                          210

I tried to do it with your measure, but I couldn't get it.

Will you please tell me?

Thanks for your help!

Omar

[parry2k]

@omarevp try this didn't spent much time but i thnk it will work

Diff = 
VAR __minDate = CALCULATE( MAX( Table8[Date] ), FILTER( ALLEXCEPT( Table8, Table8[Unit] ), Table8[Date] < MAX( Table8[Date] ) ) )
VAR __PrevValue = CALCULATE( MIN( Table8[Workinghours] ),  ALLEXCEPT( Table8, Table8[Unit] ), Table8[Date] = __minDate) 
RETURN
   IF(NOT HASONEFILTER( Table8[Date] ), BLANK(), IF (__PrevValue = BLANK() , 0, SELECTEDVALUE( Table8[Workinghours] ) - __PrevValue)   )