Calculate date difference for the projects which has tasks condition

[Anonymous]

Hi All,

Need help with calculating date difference for the projects which has only tasks "Enquiry Submitted" and "Approval Sent"

"Approval sent" should consider start date and "enquiry submitted "task should consider End date.
@amitchandak 

@Jihwan_Kim 
@dax 

Project	Task	Start Date	end date
AC1	Enquiry Submitted	12-06-2022	09-06-2022
AC1	Approval Sent	19-06-2022	12-06-2022
AC2	Enquiry Submitted	05-06-2022	09-06-2022
AC3	Enquiry Submitted	04-06-2022	05-06-2022
AC4	Enquiry Submitted	1-06-2022	05-06-2022
AC4	Approval Sent	02-06-2022	01-07-2022
AC4	Feedback submitted	04-06-2022	09-06-2022
AC6	Enquiry Submitted	11-09-2022	21-06-2022
AC6	Approval Sent	21-06-2022	22-06-2022

Expected o/p:
Projects AC1 and AC6 to be considered
Ac1 date diff 19-06-2022 minus 09-06-2022 =10days
Ac6 date diff 21-06-2022 minus 21-06-2022 = 0day

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a measure.

Date diff by condition measure: =
VAR _conditionone =
    COUNTROWS (
        FILTER ( Data, Data[Task] IN { "Enquiry Submitted", "Approval Sent" } )
    ) = 2
VAR _conditiontwo =
    COUNTROWS ( VALUES ( Data[Task] ) ) = 2
RETURN
    DATEDIFF (
        CALCULATE ( MAX ( Data[end date] ), Data[Task] = "Enquiry Submitted" ),
        CALCULATE ( MAX ( Data[Start Date] ), Data[Task] = "Approval Sent" ),
        DAY
    )
        * DIVIDE ( _conditionone, _conditiontwo )

[Anonymous]

Hi @Jihwan_Kim ,

Thank you for the response.It is working when I drag the project and "date diff by condition measure" into a table.

Is there a way to show the average of all these datediffs in a single card.

Thanks

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your feedback.

Do you mean that the card visualization shows 5 as a result?

Please check the below meausre and the attached pbix file.

Expected avg measure: = 
AVERAGEX(VALUES(Data[Project]), [Date diff by condition measure:])