topic Re: Calculating leadtimes considering officehours, weekends and holidays in DAX Commands and Tips

Calculating leadtimes considering officehours, weekends and holidays

jdevalk77 — Thu, 17 Nov 2022 22:16:24 GMT

Hi all, I can use some help on calculating leadtimes on different processes within our warehouse. Below you'll find a small example of my data:

Delivery	Order	1 Ready for orderpicking	2 Start orderpicking	3 Orderpicking completed	4 Inhouse services completed	5 Ready for packing	6 Ready for shipment	7 Is with carrier	8 Delivered
Y78194	JN7576	3-1-2022 08:09	3-1-2022 08:13	3-1-2022 08:31		3-1-2022 08:38	3-1-2022 17:35	4-1-2022 04:52	4-1-2022 10:24
Y78195	JN3436	3-1-2022 08:14	3-1-2022 08:34	3-1-2022 09:10	7-1-2022 12:04	7-1-2022 13:40	10-1-2022 09:22		10-1-2022 09:22
Y78244	JN7880	3-1-2022 09:04	3-1-2022 09:10	3-1-2022 11:36	4-1-2022 08:32	4-1-2022 09:39	4-1-2022 17:40	5-1-2022 04:04	5-1-2022 13:34

Now I would like to calculate the leadtimes between multple touchpoint in the process considering officehours (between 8AM and 5PM) and weekends;
1 to 8
3 to 4
4 to 6

Therefore I found on this community a way to add a column with DAX (link) and that is working for just 1 calculation. But as soon as I add another column to calculate another process between 2 touchpoints, then I got a circular dependency error.

Anyone who could solve this by using magic? 😉

Thanks in advance.
BR, Jurgen

Re: Calculating leadtimes considering officehours, weekends and holidays

amitchandak — Fri, 18 Nov 2022 04:15:58 GMT

jdevalk77 , refer if this can help

https://exceleratorbi.com.au/calculating-business-hours-using-dax/

Re: Calculating leadtimes considering officehours, weekends and holidays

jdevalk77 — Fri, 18 Nov 2022 07:36:42 GMT

Thanks for your quick response but it does work as soon as I add a second column with this DAX to calculate the time between 2 other timestamps. Then again I got the circular dependency error as I explained in my post. Would it be able to do it with a measure instead of adding a column?

Re: Calculating leadtimes considering officehours, weekends and holidays

v-jialluo-msft — Fri, 18 Nov 2022 08:06:49 GMT

^{Hi jdevalk77 ,}

^{For information on how to avoid circular dependencies, you can refer to the following links:}

Understanding circular dependencies in DAX - SQLBI
Avoiding circular dependency errors in DAX - SQLBI

^{Best Regards,}

^{Gallen Luo}

Re: Calculating leadtimes considering officehours, weekends and holidays

jdevalk77 — Fri, 18 Nov 2022 12:28:58 GMT

Great feedback thanks, but it take some experience to figure this out and find the right solution. Therefore I'll write a procedure in sql (source of the data) to perform the calculations. Anyway, thanks for your support so far.

Best regards,

Jurgen