Forum Discussion
Extract Date based on Data Condition
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?
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.
2 Replies
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.