Extract Date based on Data Condition

[Anonymous]

Hi All,

I am trying to create a gantt chart to show which people are working on which projects over time and the duration they are working on them. The data doesn't currently contain a start and end date but does contain 0s where a forecast hasn't started or has came to an end.

I need a measure which conducts the following logic:

• First time data not 0, extract date as start date
• Last time data not 0, extract data as end date

The data is set up in the following format:

Employee No	Month Date	Project	Demand (FTE)
35	01/06/2023	Project A	0.5
35	01/07/2023	Project A	0.5
35	01/08/2023	Project A	0
36	01/06/2023	Project A	1
36	01/07/2023	Project A	1
36	01/08/2023	Project A	0
35	01/06/2023	Project B	0.5
35	01/07/2023	Project B	0.5
35	01/08/2023	Project B	1

How would I do this in DAX for each individual working on each project?

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Please try:

Start Date = MINX(FILTER('Table',[Employee No]=EARLIER('Table'[Employee No])&&[Project]=EARLIER('Table'[Project])&&[Demand (FTE)]<>0),[Month Date])

End Date = MAXX(FILTER('Table',[Employee No]=EARLIER('Table'[Employee No])&&[Project]=EARLIER('Table'[Project])&&[Demand (FTE)]<>0),[Month Date])

Final output:

Best Regards,

Jianbo Li

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

[Anonymous]

Thank you!