Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
geraldb
New Member

Create a Dynamic Flag based off a Date Range Slicer

Hello;

 

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

 

Project NumberProject NameStart DateFinish Date
1Abc1/15/20204/21/2020
2Def3/18/20208/10/2020
3Ghi4/12/20209/5/2020
4JKL9/19/20203/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 NumberProject NameStart DateFinish Date

Flag

1Abc1/15/20204/21/20200
2Def3/18/20208/10/20201
3Ghi4/12/20209/5/20201
4JKL9/19/20203/1/20210

 

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

3 REPLIES 3
JustJan
Responsive Resident
Responsive Resident

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])))

2020-01-23 23_48_48-Window.png

Jan 

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

JustJan
Responsive Resident
Responsive Resident

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  

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors