Calculate Duration between phases from an Audits table

[rixmcx59]

Hello all, trying to find the best approach to get the duration of each Phase in the data below, I have an expression that's getting the duration from the audit dates but I need to use the createdonAccount date to get the first date. I need to calculate all durations, from when the customer was created in the accounts table, to the first change, second change, third change and so-on. then I need the final duration which is the last change until Now()

accounts.name	logicalName	createdonAccount	createdonAudits	OldPhase	NewPhase	Duration	Index
Customer A	audits_phase	2/13/2024 1:21	3/20/2024 17:27	NewCustomer	Phase 0		7865
Customer A	audits_phase	2/13/2024 1:21	3/21/2024 15:12	Phase 0	Phase 3	1	7986
Customer A	audits_phase	2/13/2024 1:21	4/3/2024 19:54	Phase 3	Phase 4	13	9961
Customer B	audits_phase	2/26/2024 18:24	2/26/2024 18:25	0	NewCustomer		1518
Customer B	audits_phase	2/26/2024 18:24	3/6/2024 17:53	NewCustomer	Phase 3	9	4611
Customer B	audits_phase	2/26/2024 18:24	3/14/2024 22:21	Phase 3	NewCustomer	8	6817
Customer B	audits_phase	2/26/2024 18:24	3/18/2024 16:50	NewCustomer	Phase 3	4	7337
Customer B	audits_phase	2/26/2024 18:24	3/27/2024 22:11	Phase 3	Phase 4	9	8964

The expression I am using now

Duration = 
VAR temp =
    TOPN (
        1,
        FILTER (
            audits,
            audits[accounts.name] = EARLIER ( audits[accounts.name]  )
                && audits[createdonAudits] < EARLIER ( audits[createdonAudits] )
        ),
        [createdonAudits], DESC
    )
RETURN
    DATEDIFF ( MINX ( temp, [createdonAudits] ), audits[createdonAudits], DAY )

Thanks for any help

[rixmcx59]

Yes, for cutomer A, line 1, the first duration would be between the createdonAccount date 2/13/2024 and the earliest createdonAudit date 3/20/2024, so Customer A result shound have a start and end date for each Phase like below

New Customer To Phase 0 = 37 days  3/20/24 - 2/13/24

Phase 0 To Phase 3            = 1 day      3/21/24 - 3/20/24

Phase 3 To Phase 4            = 13 days   4/3/24 - 3/21/24

Phase 4 To Now()               =  Now() - 4/3/24

thanks for the resonse

[v-xiandat-msft]

Hi @rixmcx59 ,

Do you have an idea of what to expect, so that we can solve your problem better?

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rixmcx59]

Hello, after looking into the Audits data, there is a created date identified by a change type column, so now the the calculated column is working. I just need to figure out how to get the current duration, which is the days since the last change date and Now(), I'm guessing I need to add a calendar table and use todays date as the end date.

Thanks