Good day!
I'm building a projct management dashboard and i need support to achieve the requiremnts.
My main data table is simialr to the following :
| project id | project name | phase | start date | end date | Area |
| 189 | A | phase 1 | 1/1/2020 | 20/12/2022 | F2 |
| 130 | C | phase 3 | 5/2/2021 | 18/6/2021 | R1 |
| 130 | C | phase 4 | 1/7/2021 | 5/5/2023 | R1 |
I want to do some analysis in the data... ex. i want to calculate number of projects in each area in a certain year.
I'm facing 2 issues here:
1st- I'm using gantt chart and do this i must disconnect the table "date" so i can filter the chart view. And connincting the 2 tables through the dax bellow.
Measure = var _start_date = MAX('Date'[Date])
var _cur_task_start =MAX('Table'[StartDate])
var _cur_task_end = MAX('Table'[EndDate])
var _days =VALUE(FORMAT( _cur_task_end - _cur_task_start , "0"))
var _cur_year =DATE(YEAR( MAX('Date'[Date])),12,31)
var _cur_year_1 = DATE(YEAR( MAX('Date'[Date])),1,1)
var _days2 = IF(_cur_task_end> _cur_year ,VALUE(FORMAT( _cur_year-_cur_task_start, "0")),_days)
var _end2 = IF(_cur_year<_cur_task_end,_cur_year,_cur_task_end)
var _days3 = VALUE(FORMAT( _end2-_cur_year_1, "0"))
return
IF( _cur_task_start <_cur_year_1 && _cur_task_end >=_cur_year_1 && _start_date=_cur_year_1 , _days3,IF(_start_date=_cur_task_start,_days2,BLANK()))
please check this post to understand the situation better : Solved: Re: Date slicer in Gantt charts - Microsoft Power BI Community
2nd- My database only have start anend date, if I'll filter dates in between it will not counted in the output.
ex. Project C, if i filter year = 2022, is it gonna be counted in the final output?
Now- I sucseed creating the Gantt chart as desired (the timeline view), which is 50% of my dashboard.
I'm stuck in the remaining 50% - the statistical view.
Please, any idea how this issue can be solved?
Thank you in advance 🙂
