Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
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!
Proud to be a Super User!
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
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |