topic Help with calculating specific delay time per day in DAX Commands and Tips

Help with calculating specific delay time per day

Paradox1023 — Sat, 27 May 2023 20:52:03 GMT

Hi, I am trying to make a report that would give the delay time per day (which would give me the run time per day to get approximate TPH). An example delay data table is formatted as shown below.

Since I have to calculate tons per hour for every day using another table with production information, I created a Calendar table (just a one-column table with only the dates using the CALENDAR() function) that is connected with both the delay table and production table.

However, I am struggling to calculate the number of hours of delay time per day with DelayType 0 as a column in the calendar table with the format shown below using the previous delay table as an example.

I tried making a calculated column using DATEDIFF() but I am struggling to get only the entries with DelayType of 0 as well as how to deal with delays that span for multiple days (For example, 05/26/2023 falls within the 4th entry of the delay table so the total delay time for that day would be 24 hours).

I would appreciate any help or guidance on what I can do to get the final formatted Calendar table. Thank you.

Re: Help with calculating specific delay time per day

tamerj1 — Sun, 28 May 2023 17:47:20 GMT

Hi Paradox1023

please try

Total Delay Time due to DelayType 0 (hour) =
SUMX (
FILTER (
CROSSJOIN ( ALLSELECTED ( 'Delay Table' ), VALUES ( 'Calendar'[Date] ) ),
'Delay Table'[Delay Type] = 0
&& 'Delay Table'[DelayStartTime] <= 'Calendar'[Date]
&& 'Delay Table'[DelayEndTime] >= 'Calendar'[Date]
),
DATEDIFF (
MAX ( 'Delay Table'[DelayStartTime], 'Calendar'[Date] ),
MIN ( 'Delay Table'[DelayEndTime], 'Calendar'[Date] ),
MINUTE
) / 60
)

Re: Help with calculating specific delay time per day

Anonymous — Tue, 30 May 2023 03:20:20 GMT

Hi Paradox1023 ,

According to your screenshot, I think you don't need to create a relationship between Calendar Table and Delay Table, due to datetime type column in Delay table and date type column in Calendar.

You can try this code to create a measure.

Total Delay Time due to DelayType 0 (hour) = VAR _CROSSJOIN = FILTER ( GENERATE ( ALLSELECTED('Delay Table') , CALCULATETABLE(VALUES ( 'Calendar'[Date] ),ALLSELECTED('Calendar')) ), 'Delay Table'[DelayType] = 0 && DATEVALUE ( 'Delay Table'[DelayStartTime] ) <= 'Calendar'[Date] && DATEVALUE ( 'Delay Table'[DelayEndTime] ) >= 'Calendar'[Date] ) VAR _AddDuartion = ADDCOLUMNS ( _CROSSJOIN, "Duration", VAR _MIN = MINX ( FILTER ( _CROSSJOIN, AND ( 'Delay Table'[DelayStartTime] = EARLIER ( 'Delay Table'[DelayStartTime] ), 'Delay Table'[DelayEndTime] = EARLIER ( 'Delay Table'[DelayEndTime] ) ) ), [Date] ) VAR _MAX = MAXX ( FILTER ( _CROSSJOIN, AND ( 'Delay Table'[DelayStartTime] = EARLIER ( 'Delay Table'[DelayStartTime] ), 'Delay Table'[DelayEndTime] = EARLIER ( 'Delay Table'[DelayEndTime] ) ) ), [Date] ) RETURN SWITCH ( TRUE (), [Date] = _MIN && [Date] = _MAX, [Duration (hour)], [Date] <> _MIN && [Date] <> _MAX, 24, [Date] = _MIN && [Date] <> _MAX, DATEDIFF ( [DelayStartTime], [Date] + 1, MINUTE ) / 60, [Date] <> _MIN && [Date] = _MAX, DATEDIFF ( [Date], [DelayEndTime], MINUTE ) / 60 ) ) RETURN SUMX(FILTER(_AddDuartion,[Date] = MAX('Calendar'[Date])),[Duration])+0

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.