Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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
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
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |