Hello guys,this time I need you again. This is my sample data:DateUnitExtractedWorkinghours3/1/2019 7:14P-19274590025.7983/1/2019 8:05BD15384296.73/1/2019 8:27APV2526.7775.3138893/1/2019 8:30P-203651975.898893/1/2019 9:57APV29721558.497783/1/2019 10:11APV2793576.253/4/2019 10:57P-20611982.221113/4/2019 11:11P-201131982.308333/4/2019 11:20TP0598-3482013/4/2019 12:51APV2536.1778.8988893/4/2019 14:16APV27462594.33/4/2019 14:35APV29531564.172783/5/2019 13:21P-19290590025.7983/5/2019 14:03P-201031992.2853/5/2019 14:19BD15125314.653/5/2019 14:33APV2549.2784.2538893/5/2019 14:42APV2568784.2705563/5/2019 16:00APV294041568.317223/5/2019 16:35RE01478301.4018693/6/2019 9:56P-19277590025.7983/6/2019 13:16BD15110322.753/6/2019 14:08APV27163604.83/7/2019 9:44P-201032005.859443/7/2019 9:54DCP0122199.653/7/2019 10:26APV27127619.7763893/7/2019 10:37APV291101581.948333/8/2019 9:30P-202042014.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: DateUnitExtractedWorkinghoursDIFF (desired)3/1/2019 10:11APV2793576.25 3/4/2019 14:16APV27462594.318.053/6/2019 14:08APV27163604.810.53/7/2019 10:26APV27127619.77638914.97638893/11/2019 10:14APV27137627.3055567.529166673/15/2019 10:32APV27295648.61222221.30666673/18/2019 10:34APV27492663.07514.46277783/22/2019 9:56APV2720370056341.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.

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 DateUnitExtractedWorkinghoursPrevious Working HoursDiff03/01/2019 08:27APV2526.7775.313889 03/04/2019 12:51APV2536.1778.898889775.3138893.58503/05/2019 14:33APV2549.2784.253889778.8988895.35503/05/2019 14:42APV2568784.270556784.2538890.01666703/01/2019 10:11APV2793576.25 03/04/2019 14:16APV27462594.3576.2518.0503/06/2019 14:08APV27163604.8594.310.503/07/2019 10:26APV27127619.776389604.814.97638903/01/2019 09:57APV29721558.49778 03/04/2019 14:35APV29531564.172781558.497785.67503/05/2019 16:00APV294041568.317221564.172784.1444403/07/2019 10:37APV291101581.948331568.3172213.6311103/01/2019 08:05BD15384296.7 03/05/2019 14:19BD15125314.65296.717.9503/06/2019 13:16BD15110322.75314.658.103/07/2019 09:54DCP0122199.65 03/01/2019 07:14P-19274590025.798 03/05/2019 13:21P-19290590025.798590025.798003/06/2019 09:56P-19277590025.798590025.798003/01/2019 08:30P-203651975.89889 03/04/2019 10:57P-20611982.221111975.898896.3222203/04/2019 11:11P-201131982.308331982.221110.0872203/05/2019 14:03P-201031992.2851982.308339.9766703/07/2019 09:44P-201032005.859441992.28513.5744403/08/2019 09:30P-202042014.9652005.859449.1055603/05/2019 16:35RE01478301.401869 03/04/2019 11:20TP0598-348201

omarevp

Helper II

7 years ago

Solved

Difference between rows with different categories

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.

Anonymous

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

3 Replies

parry2k

Super User

7 years ago

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)   )

Anonymous

Not applicable

7 years ago

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
Helper II
7 years ago
AnonymousThanks! 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

Forum Discussion

Difference between rows with different categories

3 Replies

Related content

Different schema into different destination

Bar Graph - 2 Different Categories

Page Level filter for different groups

Conditional Formatting with Different Thresholds per Category

Difference between different Staging lakehouses