Only using one of two dates for a calculation

[lynnzrae]

I am trying to find the # of days for an application to process.  For the same project, an applicant may submint their application more than once.  When that happens I only want to use the earliest of the two dates in my calculation.  Right now it will take the sum of both when I create a visual with it.

Below is my data to include the dax column I created to find the # of days in applicant cycle time.  When I create a bar graph to visualize it adds the 62 and the 316 together and the bargraph calculates this application as being in progress for 378 days.  I want it to only show as being active for 316 days (the earliest of the two dates).

In case it helps the dax I created to find the # of days in applicant cycle time is 

# of Days in Applicant Cycle Time = IF(

                                        ISBLANK(TPermitMetrics[BRG-2 Application Deemed Complete; Complete Date]),

                                        DATEDIFF(TPermitMetrics[Application Received; Complete Date], TODAY(), DAY),

                                        DATEDIFF(TPermitMetrics[Application Received; Complete Date],TPermitMetrics[BRG-2 Application Deemed Complete; Complete Date], DAY)

                                    )

Should I change the dax or would it be a filter for the visual of some sort?

Permiit UID	Project Title	Application Received; Complete Date	BRG-2 Application Deemed Completae: Complete Date	# of days in applicant cycle time
9122	Projet Title A	8/30/2024	10/31/2024	62
9122	Projet Title A	12/20/2023	10/31/2024	316

[bhanu_gautam · Accepted solution]

@lynnzrae Create a calculated column to identify the earliest application date for each project:

Earliest Application Date =
CALCULATE(
MIN(TPermitMetrics[Application Received; Complete Date]),
ALLEXCEPT(TPermitMetrics, TPermitMetrics[Permiit UID])
)

Modify your existing DAX formula to use this earliest application date:

# of Days in Applicant Cycle Time =
IF(
ISBLANK(TPermitMetrics[BRG-2 Application Deemed Complete; Complete Date]),
DATEDIFF(TPermitMetrics[Earliest Application Date], TODAY(), DAY),
DATEDIFF(TPermitMetrics[Earliest Application Date], TPermitMetrics[BRG-2 Application Deemed Complete; Complete Date], DAY)
)

[lynnzrae]

that works great.  Should I do some sort of distinct count dax as well to make sure that the project is only counted once in my visuals?  right now the advice provided solves the two different set of date issues but it still sums the two rows together when I try to visualize the data.

[Anonymous · Accepted solution]

Hi @lynnzrae ,

I created a sample pbix file(see the attachment), please check if it can return your expected result.

Flag = 
VAR _mindate =
    CALCULATE (
        MIN ( 'TPermitMetrics'[Application Received; Complete Date] ),
        FILTER (
            'TPermitMetrics',
            'TPermitMetrics'[Permiit UID] = EARLIER ( 'TPermitMetrics'[Permiit UID] )
        )
    )
RETURN
    IF ( 'TPermitMetrics'[Application Received; Complete Date] = _mindate, "Y" )

Best Regards

[bhanu_gautam · Accepted solution]

@lynnzrae Create a calculated column to identify the earliest application date for each project:

Earliest Application Date =
CALCULATE(
MIN(TPermitMetrics[Application Received; Complete Date]),
ALLEXCEPT(TPermitMetrics, TPermitMetrics[Permiit UID])
)

Modify your existing DAX formula to use this earliest application date:

# of Days in Applicant Cycle Time =
IF(
ISBLANK(TPermitMetrics[BRG-2 Application Deemed Complete; Complete Date]),
DATEDIFF(TPermitMetrics[Earliest Application Date], TODAY(), DAY),
DATEDIFF(TPermitMetrics[Earliest Application Date], TPermitMetrics[BRG-2 Application Deemed Complete; Complete Date], DAY)
)