topic Create a Dynamic Flag based off a Date Range Slicer in DAX Commands and Tips

Create a Dynamic Flag based off a Date Range Slicer

geraldb — Thu, 23 Jan 2020 22:04:31 GMT

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

Re: Create a Dynamic Flag based off a Date Range Slicer

Anonymous — Thu, 23 Jan 2020 22:49:40 GMT

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

Re: Create a Dynamic Flag based off a Date Range Slicer

geraldb — Thu, 23 Jan 2020 23:19:29 GMT

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

Re: Create a Dynamic Flag based off a Date Range Slicer

Anonymous — Fri, 24 Jan 2020 09:26:30 GMT

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