DATEDIFF with conditions

[CamillaDiPassio]

Hello everyone, 

I have an issue with a dataset. I have this table type:

MaterialCode	Creationdate	UserName	Type
Krd_017	22/07/2022 14:12	ITDASCI	1
Krd_017	22/07/2022 12:07	ITDASCI	2
Krd_017	03/08/2022 11:29	ITSTFUR	2
Krd_005	29/07/2022 18:27	ITDASCI	1
Krd_005	22/07/2022 18:20	admin	2
10000005793	03/08/2022 12:00	ITSTFUR	2
Krd_021	02/08/2022 10:56	ITENCOL2	1
Krd_021	02/08/2022 10:21	ITENCOL2	2
Krd_027	21/07/2022 12:50	admin	1
Krd_021	03/08/2022 11:28	ITSTFUR	2
Krd_027	21/07/2022 12:49	admin	2
Krd_098	22/07/2022 19:02	admin	1
Krd_098	02/08/2022 08:43	ITREANZ	1
Krd_098	18/07/2022 10:03	admin	2
Krd_098	02/08/2022 07:43	ITREANZ	2
Krd_650	02/08/2022 11:50	ITLUDEL2	1
Krd_650	01/08/2022 15:22	ITLUDEL2	2
Krd_648	11/07/2022 10:15	admin	2
10000026608	03/08/2022 14:57	ITALBER	1
10000026608	03/08/2022 14:49	ITALBER	2
10000029136	13/07/2022 11:00	admin	1
10000029136	01/08/2022 15:30	ITCLCIC1	1
10000029136	13/07/2022 09:42	admin	2
Krd_341	22/07/2022 17:51	admin	1
10000029136	01/08/2022 14:28	ITCLCIC1	2
Krd_341	22/07/2022 17:49	admin	2
Krd_342	22/07/2022 17:49	admin	2
Krd_585	22/07/2022 17:48	admin	2
Krd_697	03/08/2022 08:47	ITSTFUR	2
Krd_696	03/08/2022 10:32	ITSTFUR	2
Krd_059	25/07/2022 14:58	ITCLCIC1	1
Krd_059	25/07/2022 14:46	ITCLCIC1	2

Where:

- MaterialCode is the code of the material I have in the warehouse;

- CreationDate is the only column of date I have;

- UserName is the code of the worker who pick or put the material inside the shelf;

- Type is a number by which the worker's action is represented. One if he put material and two if he pick it.

I need to calculate how many days the material is outside the shelf before it is put back inside. The difficulty I have is that all this information is contained in one table so I will have to enter more than one condition. 

I need to calculate the difference in days from when the material is picked (type 2) to when it is put (type 1) taking into account that this operation is unique to UserName. In fact, the same material can be withdrawn and repositioned several times, but the subtraction has to be done only for one operator at a time. I hope I have explained my problem well, would you guys be able to help me?

Thanks,

Camilla

[tamerj1]

Hi @CamillaDiPassio 
Please refer to sample file attached with two solutions, one based on a calculated column and one is based on a measure

Days Outside Shelf Measure = 
VAR TotalMinutes =
    SUMX ( 
        Data,
        VAR CurrentType = Data[Type]
        VAR CurrentDate = Data[Creationdate]
        VAR CurrentTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[MaterialCode], Data[UserName] ) )
        VAR PreviousTable = FILTER ( CurrentTable, Data[Creationdate] < CurrentDate )
        VAR Previous1Date = MAXX ( FILTER ( PreviousTable, Data[Creationdate] = 1 ), Data[Creationdate] )
        VAR Previous2Date = MINX ( FILTER ( PreviousTable, Data[Creationdate] > Previous1Date && Data[Type] = 2 ), Data[Creationdate] )
        VAR Minutes = DATEDIFF ( Previous2Date, CurrentDate, MINUTE )
        RETURN
            IF ( CurrentType = 1, Minutes )
    )
VAR Days = QUOTIENT ( TotalMinutes, 1440 )
VAR Hours = QUOTIENT ( MOD ( TotalMinutes, 1440 ), 60 )
VAR Minutes = MOD ( MOD ( TotalMinutes, 1440 ), 60 )
RETURN
    IF ( 
        TotalMinutes <> BLANK ( ),
        Days & " Days, " & Hours & "Hr & " & Minutes & "Min"
    )

[CamillaDiPassio]

Thank you for your reply!

But in this case I lose the detail of the material that is out and has not yet returned, so I should also add the TODAY() condition, how can I do that?

Also, I don't need the MINUTES but to round everything up to DAYS so if the material has been out 5 minutes for me it is the same as saying it has been out 1 day.

[tamerj1 · Accepted solution]

Hi @CamillaDiPassio 
Please try this version with your data. I cannot test as the sample data that I have does not reflect this case.