Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello.
I would like some help to be able to solve a demand.
I need to set the deadline for a specific line using the start time date.
For this, there is a need to follow 3 rules:
The first rule: The deadline for the first line is 36 hours from the start time (Start_time).
The second rule:
If the start date is before the deadline of the immediately preceding line. The deadline for this will be 24 hours after the deadline for the immediately preceding line.
The third rule:
If the start date is later than the deadline of the line immediately preceding it. The term of this line will be 36 hours after the creation (Start_Time) of the line on which it is being calculated.
Example:
Index | START_DATE | DEADLINE |
1 | 04/01/2021 15:40:00 | 06/01/2021 03:40:00 |
2 | 04/01/2021 15:40:17 | 07/01/2021 03:40:00 |
3 | 06/01/2021 17:16:16 | 08/01/2021 05:16:16 |
4 | 06/01/2021 17:16:51 | 09/01/2021 05:16:16 |
5 | 06/01/2021 17:17:02 | 10/01/2021 05:16:16 |
6 | 12/01/2021 15:47:37 | 14/01/2021 03:47:37 |
7 | 12/01/2021 22:14:24 | 15/01/2021 03:47:37 |
I admit that I have tried in several ways, but creating a second line by pulling the start date from the previous line does not work alone, as there is a need for a calculation in the new column where the immediately previous line of this new column is analyzed.
Please, can someone help me with this problem?
Thanks!
Solved! Go to Solution.
Here is a solution using List.Accumulate. I think you made a mistake in row with index 3 in your example.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBRCsMwDAPQq5R8Fyo5Tr35KqH3v0abUVayyfjLiIdR74VlLfAN3AzGhS0dCVzX5Xewf2Ood+xYezFFMCQRmqiDeHhGcr9WEq+HaHdsEK6IRkm8NdH+iUiYIghNjJdpUxeRVXZBn7r4xAYRM2GW9DSXRBPEcQI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Start = _t, Deadline = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Start", type datetime}, {"Deadline", type datetime}}),
ListStart = ChangedType[Start],
FirstStart = ListStart{0},
Calculation =
List.Accumulate(
ListStart,
{ FirstStart + #duration(0,-24,0,0) },
(state, current) =>
if current < List.Last(state)
then
state & {List.Last(state) + #duration(0,24,0,0)}
else
state & {current + #duration(0,36,0,0)}
),
#"Added Custom" = Table.AddColumn(ChangedType, "CalculatedDeadLine", each Calculation{[Index]}, type date)
in
#"Added Custom"
The seed I used is just force row 1 to follow rule 3 and add 36 hours.
Good luck,
JW
@JW_van_Holst thanks for de help!!!
This work very well!
THANKS!!!!
PS.: I really make a mistake in rows 3. Sorry.
Here is a solution using List.Accumulate. I think you made a mistake in row with index 3 in your example.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBRCsMwDAPQq5R8Fyo5Tr35KqH3v0abUVayyfjLiIdR74VlLfAN3AzGhS0dCVzX5Xewf2Ood+xYezFFMCQRmqiDeHhGcr9WEq+HaHdsEK6IRkm8NdH+iUiYIghNjJdpUxeRVXZBn7r4xAYRM2GW9DSXRBPEcQI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Start = _t, Deadline = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Start", type datetime}, {"Deadline", type datetime}}),
ListStart = ChangedType[Start],
FirstStart = ListStart{0},
Calculation =
List.Accumulate(
ListStart,
{ FirstStart + #duration(0,-24,0,0) },
(state, current) =>
if current < List.Last(state)
then
state & {List.Last(state) + #duration(0,24,0,0)}
else
state & {current + #duration(0,36,0,0)}
),
#"Added Custom" = Table.AddColumn(ChangedType, "CalculatedDeadLine", each Calculation{[Index]}, type date)
in
#"Added Custom"
The seed I used is just force row 1 to follow rule 3 and add 36 hours.
Good luck,
JW
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.