Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi everyone,
The data I am working with is in the format of "Start Date" and "End Date". Is there any way that I could make it so that I have a slicer ranging from say 2015-2030 and if the slicer is set to 2023 it will show the data of all the projects that will be active that year.
Example of Data:
Plant | Plant Name | Product Type | Start Date | End Date | Annual LB Produced |
1 | A | Fruit | 1/11/2019 | 11/1/2036 | 47563 |
2 | B | Meat | 1/1/2019 | 6/1/2023 | 22128 |
3 | C | Fruit | 11/1/2023 | 11/1/2035 | 97860 |
4 | D | Vegies | 1/1/2025 | 12/1/2034 | 24650 |
5 | E | Grain | 4/1/2024 | 1/1/2037 | 272804 |
6 | F | Meat | 11/1/2022 | 6/1/2023 | 36542 |
In the case mentioned above where the slicer is set 2023 any data mapped to Plants 1,2,3 and 6 would be visable.
Any help would be greatly appreciated and thank you in advance for your time!
Solved! Go to Solution.
Hi @LadaA,
If you have a relatively small dataset, you can create an extended table like the one below and use the newly added column in it for filtering. If you deal with big data, you'll need another, less gready solution.
In plain text:
Extended Data =
FILTER (
CROSSJOIN (
data,
GENERATESERIES ( YEAR ( MIN ( data[Start Date] ) ), YEAR ( MAX ( data[End Date] ) ), 1 ) ),
[Value] >= YEAR ( [Start Date]) && [Value] <= YEAR ( [End Date] ) )
Best Regards,
Alexander
General = FILTER(
AND( GENERATESERIES(YEAR(MIN([StartDate]), YAER(MAX[ENDDATE])),
YEAR([StartDate)<=YEAR([EndDate])
)
)
Hi @LadaA,
If you have a relatively small dataset, you can create an extended table like the one below and use the newly added column in it for filtering. If you deal with big data, you'll need another, less gready solution.
In plain text:
Extended Data =
FILTER (
CROSSJOIN (
data,
GENERATESERIES ( YEAR ( MIN ( data[Start Date] ) ), YEAR ( MAX ( data[End Date] ) ), 1 ) ),
[Value] >= YEAR ( [Start Date]) && [Value] <= YEAR ( [End Date] ) )
Best Regards,
Alexander
User | Count |
---|---|
53 | |
46 | |
19 | |
16 | |
13 |
User | Count |
---|---|
108 | |
56 | |
29 | |
21 | |
18 |