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

[neees78]

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

[Fowmy · Accepted solution]

@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

[neees78]

@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 

[ValtteriN]

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!

[neees78]

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