Forum Discussion

Kopek's avatar
Kopek
Helper IV
6 years ago

Gantt Chart - date filter

Hi All!   I have another issue with my Gantt Chart.   I use the Microsoft Gantt 2.2.3 and would like to be able to filter it by certain time range. In order to do that, I have created a AutoCale...
Need Help
nhoward's avatar
nhoward
Resolver I
6 years ago

An idea, assuming that you can put a measure in for the Start parameter of the Gantt chart.  I have used the Gantt Chart by MAQ Software, which does allow this. 

 

You need a measure that will give you either the start date of the Activity or the first date of the Date Filter.  I have tested this in my Leave Planning Gantt, and it appears to work as expected. 

 

Alt StartDate = SWITCH(TRUE(),FIRSTDATE(HRLeaveRequest[LeaveDate])>FIRSTDATE(CalendarDates[DATE]),FIRSTDATE(CalendarDates[DATE]),FIRSTDATE(HRLeaveRequest[LeaveDate]))
 
Sorry, on further testing, it didn't work as required, but it is a starting point.  I'll keep experimenting... 
 
 

Kopek's avatar
Kopek
Helper IV
6 years ago

Hi nhoward , Anonymous ,

 

Thanks a lot for your replies!

 

I will try Anonymous  solution later today, and will let you know if that works (tho not sure how that is going to work, but will give it a chance ! )

 

nhoward  if you find any other solution, please let me know! Will appreciate 🙂

  • nhoward's avatar
    nhoward
    Resolver I
    6 years ago

    HI Kopek,

     

    I finally got back to trying to solve this for my Leave Requests Gantt Chart.   The biggest hurdle is that PBI doesn't do: Date > Date.  In excel you can because a date is a number, but in PBI a date is text field. 

     

    I figured away around it, but had to add columns to by leave request table, I used DATEDIFF, to get an amount of days compared to NOW(). 

     

    Some background.  Leave Requests have a LeaveDate and a dateTo (ie start and end).  So if the start date is before now, and the end date is after now, I want to use now as the start date.  Then my Gantt chart that shows request in the next 90 days, will pickup these requests too. 

     

    Using DateDiff yields either a negative or postive number.  Negative means that the request date is after now, and Postive means that the request date is before now. 

     

    I created 3 columns, but you could combine the formulas into one. 

     

    DateDiffStart = DATEDIFF(HRLeaveRequest[LeaveDate], NOW(), Day)

    DateDiffEnd = DATEDIFF(HRLeaveRequest[dateTo], NOW() ,DAY)

    DateFrom (ie alternate start date) = IF(AND(HRLeaveRequest[DateDiffStart]>0,HRLeaveRequest[DateDiffEnd]<0),DATEVALUE(NOW()),HRLeaveRequest[LeaveDate])

     

    I know that this is different to your requirments for an alternate start date equal to the start of the filtered month, but this might give you a starting point. 

     

    Hope that helps.