Create a Dynamic Flag based off a Date Range Slicer

[geraldb]

Hello;

I have a data set of projects that have both a start and a finish date, for example:

Project Number	Project Name	Start Date	Finish Date
1	Abc	1/15/2020	4/21/2020
2	Def	3/18/2020	8/10/2020
3	Ghi	4/12/2020	9/5/2020
4	JKL	9/19/2020	3/1/2021

My users want a dashboard that has a date slicer providing a range, so for instance 5/1/2020 - 8/30/2020, and then only display the projects that are active betwen that range.

I was thinking I could provide a flag, so in the case of the above data it would look like this given the above dates:

Project Number	Project Name	Start Date	Finish Date	Flag
1	Abc	1/15/2020	4/21/2020	0
2	Def	3/18/2020	8/10/2020	1
3	Ghi	4/12/2020	9/5/2020	1
4	JKL	9/19/2020	3/1/2021	0

Based on what I have seen from other posts, I think something like this can be done via DAX, but I don't have enough experience with the language yet to be able to program it.

For Refence my Table is named "Projects" and the slicer is "Active Date Range".

Any help you guys can give would be appreciated.

Thanks

Gerry

[Anonymous]

Hi @geraldb ,

This should do the "trick".

Flag = calculate( COUNTROWS(Projects), filter(Projects, Projects[Start Date]<Max('date'[Date]) && Projects[Finish Date]>Min('date'[Date])))

Jan 

[geraldb]

Jan;

Thanks for your help with this!  I am still running into an error, however.  When I create the measure I get an error saying, "Cannot find table 'date'."  From your screenshots it looks like this should be the slicer, but the meausre doesn't seem to recognize it, how do i identify the slicer in a way that the measure can read it?

Thanks

Gerry

[Anonymous]

Hi Gerald,

I assumed that you have a "date" table in you model. Typically it would be the same field that you use in your "Active Date Range" slicer.  So if you table/field is called 'Dates'[Active Date Range] then you need to substitute 'date[Date]' with 'Dates'[Active Date Range]. 

Hope this helps, otherwise a bit more information is required. 

Jan