Forum Discussion
Prepare data for Gantt visual
Anonymous
I've managed to group and index per id, so that I per ID, get all the dates in a sorted id with an indexed count. And then starts over again per next ID. Which is perfect,
My issue now is that I want to duplicate my date/timestamp column, but shifted one step so that the date/timestamp is one step behind the other one. So that the end date is the start date of the next row.
Edit: Solved with calculated column:
End Timestamp =
CALCULATE(
MIN('Table'[Start Timestamp]),
FILTER(
'Table',
'Table'[Key] = EARLIER('Table'[Key]) &&
'Table'[index] = EARLIER('Table'[index]) + 1
)
)
Hi, Anonymous
May I ask which columns can be used as start date and end date and what is the dilemma you are facing, you can describe it more clearly with pictures or words. Thanks for your understanding.
Best Regards,
Yang
Community Support Team
Anonymous Thanks for the reply!
Start date is always the earliest date, no matter the column, but should according to business logic be step 1.
Then the next step should be whatever date comes next, this date would then also be the end date for the previous row.
So if it goes from step 2 to step 3 2024-01-01, then that date would be end date for step 2, and start date for step 3
I tried sorting then grouping and adding an index, but then I probably messed up somehow with the index and duplicating columns etc, so maybe there's another solution there or how to do it properly
Edit:
I'm trying to merge all the columns into first one column, that should be sorted/grouped per ID, and the dates in order. Then I want to create a second table with the same dates, but shifted one step. So that the End date is the next row's start date.
Anonymous
I've managed to group and index per id, so that I per ID, get all the dates in a sorted id with an indexed count. And then starts over again per next ID. Which is perfect,
My issue now is that I want to duplicate my date/timestamp column, but shifted one step so that the date/timestamp is one step behind the other one. So that the end date is the start date of the next row.
Edit: Solved with calculated column:
End Timestamp =
CALCULATE(
MIN('Table'[Start Timestamp]),
FILTER(
'Table',
'Table'[Key] = EARLIER('Table'[Key]) &&
'Table'[index] = EARLIER('Table'[index]) + 1
)
)