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.
Hello, I have a dataset with sales with dates connected to a calendar table. Let's say the dataset spans 5 years. I want to create a dropdown slicer with the following options :
1. This month
2. Last month
3. This Year
4. Last Year
5. All
And more if needed. Question is how one would implement this. Is this a measure or a calculated column?
My first idea would be a calculated column with a switch statement for each category but I'm not sure how I would then include "This month" with "This year" and so on.
Any ideas? - Thanks in advance.
Solved! Go to Solution.
What I ended up doing was creating two calculated columns, one for month periods ad other for year periods, both with a switch function. Then I used two slicers for the calculated columns.
What I ended up doing was creating two calculated columns, one for month periods ad other for year periods, both with a switch function. Then I used two slicers for the calculated columns.
Hi @314mp_M0th4
A calculated table would actually be your best option. To create such a table you can use DAX such as the below.
Timeframe Selection =
VAR __CurrentYear = YEAR(TODAY())
VAR __LastYear = __CurrentYear - 1
VAR __ThisMonth = MONTH( TODAY() )
RETURN
UNION(
ADDCOLUMNS(
DATESMTD(dimDate[DATE] ),
"Timeframe", "This Month",
"Index", 1
),
ADDCOLUMNS(
DATEADD( SUMMARIZECOLUMNS(dimDate[DATE], FILTER(dimDate ,dimDate[MONTH] = __ThisMonth && dimDate[YEAR] = __CurrentYear)), -1, MONTH ),
"Timeframe", "Last Month" ,
"Index", 2
),
ADDCOLUMNS(
DATESYTD( dimDate[DATE] ),
"Timeframe", "This Year",
"Index", 3
),
ADDCOLUMNS(
SUMMARIZECOLUMNS(dimDate[DATE], FILTER(dimDate ,dimDate[YEAR] = __LastYear)),
"Timeframe", "Last Year" ,
"Index", 4
)
)
If you then create a relationship between this table (the date column) and your calendar dimension table, you can use the Timeframe column in a slicer to quickly filter. It will be a many-to-many relationship, but as long as you ensure your slicer is single-select it should work well.
FYI you may need to tweak the DAX code to fit your model and specific requirements but hopefully this is a guide.
The index column is optional but allows you to apply a custom sorting to how the timeframes appear in the slicer (as opposed to alphabetical order).
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |