How to only calculate a date once for a project when there are multiple project entries

[lynnzrae]

I have this set of data.  I want to calculate the number of days between application being deemed complete and the decision being rendered so that I can look at the average number of days for cycle time.  However, I only want to calculate this once per project (Permit UID).  Right now the project (Permit UID) can have multiple line items if there are other activities within the project that have more than one entry.  In this example, for two of the projects the application was submitted twice so there are two line items for the same project.  How do I only calculate the application deemed complete once?  So instead of the average of 1/24/2025, 1/24/2025, 9/20/2024, 9/20/2024, 10/17/2024 (=30.6 days) I am calculating 1/24/2025, 1/24/2025, 9/20/2024, 9/20/2024, 10/17/2024 (=29 days).  Thank you.

Permit UID	Application Received	Application Deemed Complete	Decision Rendered
9615	10/15/2024	1/24/2025	2/19/2025
9615	12/12/2024	1/24/2025	2/19/2025
9435	2/14/2024	9/20/2024	10/30/2024
9435	4/26/2024	9/20/2024	10/30/2024
9567	9/26/2024	10/17/2024	11/7/2024

[lynnzrae]

That did it.  Thank you.

[DataInsights]

@lynnzrae,

Try this measure:

Average number of days for cycle time = 
AVERAGEX (
    SUMMARIZE (
        Projects,
        Projects[Permit UID],
        Projects[Decision Rendered],
        Projects[Application Deemed Complete]
    ),
    DATEDIFF (
        Projects[Application Deemed Complete],
        Projects[Decision Rendered],
        DAY
    )
)

[lynnzrae]

Can I do this without using the average Dax so that I can use this for other visuals etc. as well.  Can I just do it where it returns the number of days and then when I create the call card I can do the average?  Not sure if that makes sense.  Something like this? so that it only returns a value on one of any project that has duplicate rows?  Thank you.

Permit UID	Application Received	Application Deemed Complete	Decision Rendered	# of days from deemed complete to decision rendered
9615	10/15/2024	1/24/2025	2/19/2025	26
9615	12/12/2024	1/24/2025	2/19/2025
9435	2/14/2024	9/20/2024	10/30/2024	40
9435	4/26/2024	9/20/2024	10/30/2024
9567	9/26/2024	10/17/2024	11/7/2024	21

[DataInsights · Accepted solution]

@lynnzrae,

Try this calculated column. The logic is to calculate the number of days for the row with the earliest Application Received date (when multiple rows exist for a Permit UID).

Number of Days for Cycle Time =
VAR vMinDate =
    CALCULATE (
        MIN ( Projects[Application Received] ),
        ALLEXCEPT ( Projects, Projects[Permit UID] )
    )
VAR vResult =
    IF (
        Projects[Application Received] = vMinDate,
        DATEDIFF (
            Projects[Application Deemed Complete],
            Projects[Decision Rendered],
            DAY
        )
    )
RETURN
    vResult