Creating dynamic measures to calculate duration between different timestamps

[grgmssgnn]

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 process.

I want to create in DAX a dynamic measure that would allow users to choose the stages they want to calculate a lag time between. E.g.: one might be interested to investigate how many days (on average) elapse between date 1 and date 0, or between date 2 and date 0, and so on...

I tried using field parameters for start date and arrival date, in order for the user to just choose in a filter the starting point and the arrival point of the measure, which would be ideal for me. Nonetheless, it seems I can't be able to get this to work.

How would you proceed?

Thanks in advance for your kind help.

Giorgio

[grgmssgnn]

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

[v-shex-msft · Accepted solution]

HI @grgmssgnn,

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

[v-shex-msft]

Hi @grgmssgnn ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng