Gantt Chart - date filter

[Kopek]

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 AutoCalendar Table, i did a relationship betweet  strat date from the main table, and that works fine.

I have added data from Calendar Table as a slicer and it filters my visualization... but not in the way I wanted...

This is example of my data:

Application	Activity Details	Milestone	Start Date	End Date	Country
App1	Activity 1		2017-May-01	2020-Sep-30	Country
App 2	Activity 2		2019-May-01	2019-Dec-01	Country
App 2	Activity 2	Milestone 1	2020-Dec-01	2020-Dec-01	Country
App3	Activity 2	Milestone 3	2020-Mar-31	2020-Mar-31	Country

and this is Gantt chart out of above data without any filters:

when i filter it, by 1st June 2017 I am loosing App1 bar since it starts in 1st May 2017 only, and from filterring perspective it makes sense, but it is not what i would like to achieve

By filterring i would like to see all ongoing activities starting from given date - so if i filter by 1st June 2017 I would like to kind of scroll gantt chart to the June 2017 and see what will be in the future, and "hide" parts of bars which are in the past (not to delete all activities starting before that day completly)

So i would like to see entry for Activity 1, because it will be in progress on 1st June 2017, but i would like to have it kind of cut and see only part starting1st June 2017 till 30 Sept 2020

Hope that it makes sense, if not please shout!

Thanks in advance.

[nhoward]

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]

Hi @nhoward , @v-jayw-msft ,

Thanks a lot for your replies!

I will try @v-jayw-msft  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]

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. 

[v-jayw-msft]

Hi @Kopek ,

As far as i know, Power BI slicers does not support to filter date range by a single start date. However, you can try cutting the date range between start date and end date into multiple time periods. Please refer to the topic below and see if it's helpful.

https://community.powerbi.com/t5/Desktop/Spread-revenue-across-period-based-on-start-and-end-date-sl....

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Kopek]

Hi @v-jayw-msft ,

I wanted to try solution you have provided under link, but honestly i have no idea how to set up that tables properly.

I do not have access to powerBI sample provided in that topic, so not able to build data range table.

Could you please help me doing that based on my sample file   - hope that link will work for you . Link 

Thanks in advance.