Forum Discussion
Creating dynamic measures to calculate duration between different timestamps
Dear all, I have a database with logs of clients going through different stages of a process. Each process has timestamp indicating the date when the single client has reached that stage of the ...
HI Anonymous,
I'd like to suggest you convert the table records to do complex unpivot column operation on these status and date fields:
Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZRNa8MwDIb/Ssi5EMtxvo6jO42xlV1LD2YpmyGLuzbpYb9+UVEhzqTMhxwiCz1+rVfa71NIN6kqMgWZVjqffnb+4gZ39RTX93hC39b3l2N/GW2XPLvv0bV2cL6fDrTOlLlnP5xOZ3+1g8UyeTZVuh0cNvtUTyGoeSJUDPFpbN27sx2WkhgQMnLKjWY87l6pCi+hCsobzDV8eZ2vS9DFjPHi+8TOZYScAtEgyKgZzpv78u3Y2Z+pJUeE1ZKgIgCVGBJ6orj3WoJUIYB0qKjC3FJQxL3cH0VGckATgGoMCQ5Ap8b7GhvAE8uA2JDVo5s1MwWYFVOgkWccUOQiVpphQDtne5+0Ljm7Yz+cfdK5j8+BrmvC7BsBqBJrh//8XcX7G/TKILHbJ97gEJJy6jvbHRVDknYD3n9OMiskzZFk54nbLpxewD2hpanizB49vstnLInONgzExSrXXxiiWlncrLepPqyYDh99zqgpFq1Bnp+Gn5+G5iB6nYoE7OqScPgF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SNDG = _t, #"Data 0: Inizio monitoraggio" = _t, #"1: Contatto cliente" = _t, #"Data 1: Contatto cliente" = _t, #"2: Rintraccio" = _t, #"Data 2: Rintraccio" = _t, #"3: Analisi posizione e condivisione strategia" = _t, #"Data 3: Analisi posizione e condivisione strategia" = _t, #"4: Delibera" = _t, #"Data 4: Delibera" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"SNDG", Int64.Type}, {"Data 0: Inizio monitoraggio", type text}, {"1: Contatto cliente", type text}, {"Data 1: Contatto cliente", type text}, {"2: Rintraccio", type text}, {"Data 2: Rintraccio", type text}, {"3: Analisi posizione e condivisione strategia", type text}, {"Data 3: Analisi posizione e condivisione strategia", type text}, {"4: Delibera", type text}, {"Data 4: Delibera", type text}}), fxTrans= (tb as table) => let #"Unpivoted Columns" = Table.UnpivotOtherColumns(tb, {}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Step", each Text.Combine(List.Select(Text.ToList([Attribute]), each Value.Is(Value.FromText(_), Int32.Type )))), #"Grouped Rows2"= Table.Group(#"Added Custom", {"Step"}, {{"Count", each Table.Transpose(Table.SelectColumns(_,{"Value"})), type table }}), #"ExpandTableColumn" = Table.ExpandTableColumn(#"Grouped Rows2", "Count", {"Column1", "Column2"}, {"Status", "Date"}) in #"ExpandTableColumn", #"Grouped Rows" = Table.Group(#"Changed Type", {"SNDG", "Data 0: Inizio monitoraggio"}, {{"Content", each fxTrans(Table.RemoveColumns(_,{"SNDG", "Data 0: Inizio monitoraggio"})), type table }}), #"Expanded Content" = Table.ExpandTableColumn(#"Grouped Rows", "Content", {"Step", "Status", "Date"}, {"Step", "Status", "Date"}) in #"Expanded Content"After these steps, you can simply use Dax expression with current group field value(SNDG) and steps as conditions. Then you can use them to find out correspond date values and calculated in DATEDIFF function.
Regards,
Xiaoxin Sheng
Here is the dummy data I'm starting from.
As you can see, there are several steps of the process, and each step has a datestamp (Data 1, Data 2, Data 3, etc.)
My measure would ideally use DATEDIFF or a similar function but the starting and end datestamps should be variable or subject to user decision.
Thanks for your help!
| SNDG | Data 0: Inizio monitoraggio | 1: Contatto cliente | Data 1: Contatto cliente | 2: Rintraccio | Data 2: Rintraccio | 3: Analisi posizione e condivisione strategia | Data 3: Analisi posizione e condivisione strategia | 4: Delibera | Data 4: Delibera |
| 1 | 05/01/2023 | Positivo | 05/02/2023 | Consensual Liquidation | 22/04/2023 | Approvata | 03/05/2023 | ||
| 2 | 18/01/2023 | Positivo | 17/02/2023 | Judicial | 02/04/2023 | Approvata | 13/05/2023 | ||
| 3 | 02/01/2023 | Positivo | 17/02/2023 | DPO | 13/04/2023 | Approvata | 07/05/2023 | ||
| 4 | 14/01/2023 | Positivo | 23/02/2023 | Judicial | 25/04/2023 | Non approvata | 17/05/2023 | ||
| 5 | 01/01/2023 | Positivo | 18/02/2023 | Rimodulazione | 28/04/2023 | Approvata | 05/05/2023 | ||
| 6 | 08/01/2023 | Positivo | 07/02/2023 | Rimodulazione | 05/04/2023 | Approvata | 27/05/2023 | ||
| 7 | 06/01/2023 | Positivo | 13/02/2023 | Rimodulazione | 24/04/2023 | Approvata | 19/05/2023 | ||
| 8 | 12/01/2023 | Positivo | 22/02/2023 | Consensual Liquidation | 01/04/2023 | Approvata | 16/05/2023 | ||
| 9 | 03/01/2023 | Positivo | 18/02/2023 | Judicial | 14/04/2023 | Non approvata | 02/05/2023 | ||
| 10 | 25/01/2023 | Positivo | 24/02/2023 | Piano di rientro light | 03/04/2023 | ||||
| 11 | 24/01/2023 | Positivo | 03/02/2023 | Judicial | 27/04/2023 | Non approvata | 17/05/2023 | ||
| 12 | 14/01/2023 | Positivo | 05/02/2023 | Rimodulazione | 28/04/2023 | Approvata | 01/05/2023 | ||
| 13 | 22/01/2023 | Positivo | 10/02/2023 | Rimodulazione | 23/04/2023 | Approvata | 14/05/2023 | ||
| 14 | 22/01/2023 | Positivo | 20/02/2023 | Consensual Liquidation | 02/04/2023 | Approvata | 15/05/2023 | ||
| 15 | 26/01/2023 | Positivo | 12/02/2023 | Rimodulazione | 05/04/2023 | Approvata | 01/05/2023 | ||
| 16 | 15/01/2023 | Positivo | 01/02/2023 | DPO | 05/04/2023 | Approvata | 07/05/2023 | ||
| 17 | 02/01/2023 | Positivo | 24/02/2023 | DPO | 01/04/2023 | Non approvata | 20/05/2023 | ||
| 18 | 20/01/2023 | Positivo | 01/02/2023 | Piano di rientro light | 09/04/2023 | ||||
| 19 | 27/01/2023 | Positivo | 07/02/2023 | Piano di rientro light | 26/04/2023 |
HI Anonymous,
I'd like to suggest you convert the table records to do complex unpivot column operation on these status and date fields:
Full query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZRNa8MwDIb/Ssi5EMtxvo6jO42xlV1LD2YpmyGLuzbpYb9+UVEhzqTMhxwiCz1+rVfa71NIN6kqMgWZVjqffnb+4gZ39RTX93hC39b3l2N/GW2XPLvv0bV2cL6fDrTOlLlnP5xOZ3+1g8UyeTZVuh0cNvtUTyGoeSJUDPFpbN27sx2WkhgQMnLKjWY87l6pCi+hCsobzDV8eZ2vS9DFjPHi+8TOZYScAtEgyKgZzpv78u3Y2Z+pJUeE1ZKgIgCVGBJ6orj3WoJUIYB0qKjC3FJQxL3cH0VGckATgGoMCQ5Ap8b7GhvAE8uA2JDVo5s1MwWYFVOgkWccUOQiVpphQDtne5+0Ljm7Yz+cfdK5j8+BrmvC7BsBqBJrh//8XcX7G/TKILHbJ97gEJJy6jvbHRVDknYD3n9OMiskzZFk54nbLpxewD2hpanizB49vstnLInONgzExSrXXxiiWlncrLepPqyYDh99zqgpFq1Bnp+Gn5+G5iB6nYoE7OqScPgF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SNDG = _t, #"Data 0: Inizio monitoraggio" = _t, #"1: Contatto cliente" = _t, #"Data 1: Contatto cliente" = _t, #"2: Rintraccio" = _t, #"Data 2: Rintraccio" = _t, #"3: Analisi posizione e condivisione strategia" = _t, #"Data 3: Analisi posizione e condivisione strategia" = _t, #"4: Delibera" = _t, #"Data 4: Delibera" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SNDG", Int64.Type}, {"Data 0: Inizio monitoraggio", type text}, {"1: Contatto cliente", type text}, {"Data 1: Contatto cliente", type text}, {"2: Rintraccio", type text}, {"Data 2: Rintraccio", type text}, {"3: Analisi posizione e condivisione strategia", type text}, {"Data 3: Analisi posizione e condivisione strategia", type text}, {"4: Delibera", type text}, {"Data 4: Delibera", type text}}),
fxTrans= (tb as table) =>
let
#"Unpivoted Columns" = Table.UnpivotOtherColumns(tb, {}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Step", each Text.Combine(List.Select(Text.ToList([Attribute]), each Value.Is(Value.FromText(_), Int32.Type )))),
#"Grouped Rows2"= Table.Group(#"Added Custom", {"Step"}, {{"Count", each Table.Transpose(Table.SelectColumns(_,{"Value"})), type table }}),
#"ExpandTableColumn" = Table.ExpandTableColumn(#"Grouped Rows2", "Count", {"Column1", "Column2"}, {"Status", "Date"})
in
#"ExpandTableColumn",
#"Grouped Rows" = Table.Group(#"Changed Type", {"SNDG", "Data 0: Inizio monitoraggio"}, {{"Content", each fxTrans(Table.RemoveColumns(_,{"SNDG", "Data 0: Inizio monitoraggio"})), type table }}),
#"Expanded Content" = Table.ExpandTableColumn(#"Grouped Rows", "Content", {"Step", "Status", "Date"}, {"Step", "Status", "Date"})
in
#"Expanded Content"After these steps, you can simply use Dax expression with current group field value(SNDG) and steps as conditions. Then you can use them to find out correspond date values and calculated in DATEDIFF function.
Regards,
Xiaoxin Sheng