topic Re: Backward scheduling based on working time DAX in DAX Commands and Tips

Backward scheduling based on working time DAX

MMZTM — Wed, 19 Jul 2023 05:55:11 GMT

Hello everyone,

im trying to create a calculated Column by DAX that will give me the Completion-Date for every Job-No using the tables shown below.

Based on the Delivery-Date of the Order-Table, the Completion-Dates for the respective Job are to be scheduled backward based on the needed Time for every Job. In addition, scheduling should take into account working hours and weekends.

I already have a simple scheduling based on days and without considering working hours and weekends (Code below) - Thanks to tamerj1 .

For example for Order-No. 111 the results for every Job-No should be:

Job-No. 4: Completion-Date = 14.08.2023 12:00 AM (Same as Delivery-Date)

Job-No. 3: Completion-Date = 11.08.2023 03:59 PM (Because of the Weekend)

Job-No. 2: Completion-Date = 11.08.2023 11:12 AM

Job-No. 1: Completion-Date = 09.08.2023 03:12 PM

It would be great if someone could help me.

Thanks a lot in advance!

Completion Date = VAR CurrentOrderTable = CALCULATETABLE(OPERATION_TABLE, ALLEXCEPT(OPERATION_TABLE, OPERATION_TABLE[Operation-No.])) VAR TableAfter = FILTER(CurrentOrderTable, OPERATION_TABLE[Job-No.] > EARLIER(OPERATION_TABLE[Job-No.])) VAR NumberOfDays = SUMX( TableAfter, OPERATION_TABLE[Time in days]) VAR Delivery = RELATED(ORDER_TABLE[DELIVERY]) RETURN Delivery - NumberOfDays

Re: Backward scheduling based on working time DAX

lbendlin — Fri, 21 Jul 2023 23:28:41 GMT

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Backward scheduling based on working time DAX

MMZTM — Mon, 24 Jul 2023 09:33:56 GMT

Dear lbendlin ,

Thanks for the links. I hope I have taken all the hints into account now.

I have been looking for a similar discussion on my topic in this forum for quite some time - unfortunately without success so far.

I already have a simple scheduling based on days and without considering working hours and weekends (Code below) - Thanks to @tamerj1 .

lbendlinPlease let me know if I have understood the hints in the links correctly and this is the right way to post 🙂

It would be great if someone could help me.

Thanks a lot in advance!

Here is the Sample file in .pbix-format, the Basic-Data as .csv and .xlsx-format.

PowerBI_Community

Also in the following the Relationships:

The expecting Result for the Compleation-Date for Order.No 111 should be:

Order-No	Job-No	Time in Minutes	Completion-Date
111	1	1056	09.08.2023 03:12 PM
111	2	720	11.08.2023 11:12 AM
111	3	287	11.08.2023 03:59 PM
111	4	241	14.08.2023 12:00 AM

Re: Backward scheduling based on working time DAX

lbendlin — Thu, 27 Jul 2023 20:32:15 GMT

Thank you for the sample data and the expected result. I think I understand the request, and will think about a potential approach.

Why is the Delivery-DateAndTime pegged at noon rather than midnight?

Re: Backward scheduling based on working time DAX

MMZTM — Fri, 28 Jul 2023 06:26:04 GMT

Thank you so much for checking out!! I really hope that there is a solution for this.

There is actually no reason for this. I just set it that way.

The only important thing for me would be that the time is within the working hours - so between 8:00 and 16:00.

But it would be no problem, if it is necessary for the calculation to set the time to midnight.

Re: Backward scheduling based on working time DAX

lbendlin — Fri, 28 Jul 2023 12:15:36 GMT

Here is the basic approach

Completion-Date = var j = SELECTEDVALUE(OPERATION_TABLE[Job-No.]) var mt = max(ORDER_TABLE[Delivery-DateAndTime]) // cumulative minutes from the current step to the end var m = 0+CALCULATE(sum(OPERATION_TABLE[Time in Minutes]),OPERATION_TABLE[Job-No.]>j) // series of minutes going back from the delivery date. Large padding ( * 10 ) to cover weekend and non working hour gaps. May need adjustment. var s = ADDCOLUMNS(GENERATESERIES(0,m*10),"ts",mt-divide([Value],1440)) // filter out rows for weekends and non-working hours var f = filter(s,WEEKDAY([ts],1) in {2,3,4,5,6} && HOUR([ts])>=8 && HOUR([ts])<16) // count backwards and cut off var t = topn(m+1,f,[ts],DESC) // flip sort order and get first (earliest) row. var r = topn(1,t,[ts],asc) // return the timestamp for that value return concatenatex(r,[ts])

Based on your sample data this results in

As you can see it still needs fine tuning, especially there needs to be a discussion on which minutes to include and which to exclude. See attached.