Filter data between start and end dates

[cianPM]

Hi,

I am relatively new to Power BI but have used other similar software (Tableau) on and off for about 2 years now. I am using Power BI in the construction sector and am trying to use the contractors construction schedule (exported to Excel) combined with the layout of the site (imported into Power BI using Shapemap visual and a custom .json file I created) which divides the site into grids / zones which align with the location references included in the contractor's schedule.

The goal I am working towards is to have a filter / slicer which is a date. The Shapemap should show which zones have ongoing activity on that date and be broken into different colours as per the descriptor / discipline in the contractor's schedule. This visual will allow me to identify clashes / potential logistics issues in the programme at a glance.

The below image shows how the date from the schedule is received in Power BI.

In Tableau I would simply have created a parameter which would be a date that is adjustable within the dashboard and an associated calculation along the lines of if([Start] >= [Parameterdate] && [Finish] <= [Parameterdate], "Ongoing", "Not"). I would then apply a page level filter where only 'Ongoing' tasks are shown and have a slider / dropdown to move from date to date.

Power BI doesn't allow me to create a parameter in a similar way so I tried to create a secondary table with dates and to run the above calculation only with [Parameterdate] substituted with a column which was simply a list of all the dates from the start to the end of the project. I tried setting it up with no relationship between the two tables as well as a many x many relationship but to no avail. When I relate the tables I can only relate it to the 'Start' or 'Finish' so it doesn't run correctly as a result.

I would really appreciate any assistance the Power BI community can provide. I spent the best part of a week getting the Shapefile set up and now I've hit this hurdle but unlike my issue last week where I needed 3rd party software to create my .json file I'm sure the issue is resolveable with your assistance.

If sample data is needed I'll build something this evening as the information I am working from is sensitive.

[cianPM]

@amitchandak thanks for your help. I had previous seen the HR current employee example but struggled to make it work. In the end I used a variation of it (combined with another community response):

Ongoing check =

VAR SelectedDate =

SELECTEDVALUE('Week Numbers'[Value])

RETURN

CALCULATE(COUNTROWS('High Level Sequence'),'High Level Sequence'[Finish] >= SelectedDate,'High Level Sequence'[Start] <= SelectedDate)

[amitchandak · Accepted solution]

@cianPM , to me it seems like HR current employee use case.

refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...