topic Re: DAX to create job start time based on "Day start time & Duration" in DAX Commands and Tips

DAX to create job start time based on "Day start time & Duration"

neees78 — Thu, 09 Dec 2021 07:54:36 GMT

Hello All,

I've the following table - all columns in green are available - entered manually by users

Question

is there a way to get "start date per job item" calculated using 'start date day' & 'Dur (hr)'

but sorted by system sequence , so the result will be similar to this column in Yellow
**each new job day starts at 00:00 hrs

Re: DAX to create job start time based on "Day start time & Duration"

ValtteriN — Thu, 09 Dec 2021 08:14:38 GMT

Hi,

I am not fully certain I understood your question but, basically what you want to do is to have a calculated column and then sort it based on [system sequency]? There is a functionality called sort by column so 1st you can create a new calculated column and afterwards you can select "sort by column" to sort this calculated column based on the sequency column.

Hopefully this helps and if it does consider accepting this as a solution!

Re: DAX to create job start time based on "Day start time & Duration"

Fowmy — Thu, 09 Dec 2021 08:39:13 GMT

neees78

Add the following calculated column to your table to get the addition of Start date and Duration of previous row:

Start Date Per Job Item = VAR __SYS = Table3[System Sequent] VAR __LASTSYS = CALCULATE( MAX( Table3[System Sequent] ), Table3[System Sequent] < __SYS, ALLEXCEPT(Table3 , Table3[Event] ) ) VAR __LASTDUR = CALCULATE( SUM( Table3[Dur (hr)] ), Table3[System Sequent] <= __LASTSYS, ALLEXCEPT(Table3 , Table3[Event] ) ) RETURN Table3[Start Date (day)] + TIME(__LASTDUR,0,0)

I formatted the column as follows to show 24hr time

Re: DAX to create job start time based on "Day start time & Duration"

neees78 — Fri, 10 Dec 2021 12:14:14 GMT

Fowmy

thanks a lot it worked very well for the test table but the problem I have is that my rows are not in necessarily in order

(Duration is not always going to be previous row)

if the system sequence is used to sort rows BUT after selecting job-start-day , then previous row calculation will be correct , I'm not sure if this is achievable !

it’s a large table but for a given date day all job item entered as per system sequence

example for 1 day record -sorted by resulted calcualted column

same if sorted by sys-sequence

Re: DAX to create job start time based on "Day start time & Duration"

neees78 — Fri, 10 Dec 2021 12:39:04 GMT

The solution provided by Fowmy seems to be working but on small table

I need to accomplish two steps before executing calculation

# select all rows for any given day by Start-job-day time + Sort by system Seq à Then calculate column based on Duration

But I’m not sure if this is achievable