Date

[akashsingh]

Dear Community, 

I need to filter a Gantt Chart to show the list of ongoing projects between a specified period. 

However when i use the Slicer for the start and end date, it only uses the exact inputs from each field to determine the range.

i.e. it looks for a project that starts on specifc date and ends on a specific date, and hence doesnt list those inbetween the ranges.

What i am looking for is to:
1. specific the start and end of the period

2. see all of the projects that are ongoing in that period

what is the best way to do this? i have played around with the style of the slicers for both and none quite support what i need the output to be. 

Here is a sample view of the dataset 

Employee Name	ProjectType	Project Desc	Allocation	Start Date	End Date
John Smith	Charageable	Building House	95%	2022/09/01	2023/28/31
John Smith	Training	Electrician Training	5%	2023/02/20	2023/02/23
Aiden King	Unassigned	N/A	100%	2022/12/01
Ian Fletcher	Charageable	Doing Plumbing	100%	2022/09/01	2026/08/31
Gary Green	Unassigned	N/A	100%	2023/01/25
Timothy Stuart	Charageable	Building Houses	100%	2023/01/15	2024/11/30
Timoty Stewart	Vacation	Approved Leave	100%	2023/03/14	2023/03/17

Your help will be appreciated. 

[Ashish_Mathur]

Hi,

My idea is to create one row for each date between the start and end date.  However, this will give rise to a lot of rows (more so because you have dates starting from year 2022 ad going up till year 2026).  We can reduce the number of rows drastically by creating one row dor each month between the start date and end date.  However, in the slicer then you will not be able to see the status between any two random dates.  You will only be able to select months.  Will this appoach work for you?

[ppm1]

This works best with a disconnected Date table (or you need to remove the filter from the Date table if there is a relationship). You store the min and max dates from the Date table in variables and then COUNTROWS of your table where the EndDate is >= min date and the StartDate is <= max date.

Events in progress – DAX Patterns

Pat