Forum Discussion
Calculate date difference for the projects which has tasks condition
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
| 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
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:])
3 Replies
- Jihwan_Kim
Super User
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 )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
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:])