Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I have created a dropdown date selection slicer so that my users can review the historical information.
I want to set this slicer selection default to the current month, but it appears there is no option to define a dynamic default date selection.
The selection always stays static (as per the selection at the point of upload) unless the user manually selects the current month themselves.
To workaraound this limitation, I have created the latest month column using the following DAX statement,
This returned the latest month data correctly, but the slicer I am using does not allow more than one field to appear on the dropdown.
I then added the 'Last Month' data to the MONTH hierarchy.
However, the slicer still only allowed either the original Month data or the 'Last Month' data, but not both at the same time.
Is there a way I can join my MONTH data with the 'Last Month' data. so the month dropdown selection appears something like the following,
Or is there a better way to get the slicer to always select the latest month as a default?
Regards,
Tae
Solved! Go to Solution.
Hi,
According to your description, I create a table to test:
Please take following steps:
1)create a calculated table:
Slicer =
UNION (
VAR t =
DISTINCT (
ADDCOLUMNS (
SELECTCOLUMNS ( 'Table', "Month Year", FORMAT ( 'Table'[Date], "MMMM YYYY" ) ),
"minDate", CALCULATE (
MIN ( 'Table'[Date] ),
FILTER ( 'Table', [Month Year] = FORMAT ( 'Table'[Date], "MMMM YYYY" ) )
)
)
)
RETURN
SELECTCOLUMNS (
ADDCOLUMNS ( t, "Rank", RANKX ( t, [minDate],, ASC, DENSE ) ),
"Month Year", [Month Year],
"Rank", [Rank]
),
DATATABLE ( "Month Year", STRING, "Rank", INTEGER, { { "Last Month", 0 } } )
)
And it shows:
2)create a measure:
IsFiltered =
IF (
FORMAT ( SELECTEDVALUE ( 'Table'[Date] ), "MMMM YYYY" )
IN FILTERS ( Slicer[Month Year] ),
1,
IF (
"Last Month" IN FILTERS ( Slicer[Month Year] ),
IF (
SELECTEDVALUE ( 'Table'[Date] )
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
&& SELECTEDVALUE ( 'Table'[Date] )
< DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
1,
-1
),
-1
)
)
3)choose table visual and set measure ‘IsFiltered’ as 1:
4)choose column ‘Month Year’ of table ‘Slicer’ as a slicer:
5)choose the ‘Last Month’ and the result shows:
Here is my test pbix file:
Best Regards,
Giotto Zhi
can u share pbix file, its not found it says
Hello everyone,
below query may works for your requriment
Default Current Month Slicer =
IF (
MONTH ( NOW () ) = MONTH ( Dates[Date] )
&& YEAR ( NOW () ) = YEAR ( Dates[Date] ),
"Current Month",
Dates[Year-Month]
)
Incase of any challanges related to DAX please connect with me on HAREESH MALLURI
Hi,
According to your description, I create a table to test:
Please take following steps:
1)create a calculated table:
Slicer =
UNION (
VAR t =
DISTINCT (
ADDCOLUMNS (
SELECTCOLUMNS ( 'Table', "Month Year", FORMAT ( 'Table'[Date], "MMMM YYYY" ) ),
"minDate", CALCULATE (
MIN ( 'Table'[Date] ),
FILTER ( 'Table', [Month Year] = FORMAT ( 'Table'[Date], "MMMM YYYY" ) )
)
)
)
RETURN
SELECTCOLUMNS (
ADDCOLUMNS ( t, "Rank", RANKX ( t, [minDate],, ASC, DENSE ) ),
"Month Year", [Month Year],
"Rank", [Rank]
),
DATATABLE ( "Month Year", STRING, "Rank", INTEGER, { { "Last Month", 0 } } )
)
And it shows:
2)create a measure:
IsFiltered =
IF (
FORMAT ( SELECTEDVALUE ( 'Table'[Date] ), "MMMM YYYY" )
IN FILTERS ( Slicer[Month Year] ),
1,
IF (
"Last Month" IN FILTERS ( Slicer[Month Year] ),
IF (
SELECTEDVALUE ( 'Table'[Date] )
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
&& SELECTEDVALUE ( 'Table'[Date] )
< DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
1,
-1
),
-1
)
)
3)choose table visual and set measure ‘IsFiltered’ as 1:
4)choose column ‘Month Year’ of table ‘Slicer’ as a slicer:
5)choose the ‘Last Month’ and the result shows:
Here is my test pbix file:
Best Regards,
Giotto Zhi
Thanks Giotto,
This is exactly what I wanted to know!
Interesting question - and you asked whether there is a better way to select the current month.
I like the Timeline slicer - TimeLine Slicer - Microsoft which offers a more visually appealing way to interact as well as options to force the current period or last available period
May
Maybe this helps you in a simpler, easier way.
Cheers and Happy New Year!
Manfred
Thanks for the suggestion, Manfred.
However, I can't insert a slicer larger than the dropdown list I have desgined.
Also, this slicer won't be relevant to my users as they only want to display our data as at a particular month rather than a range.
@Tae,
Have you checked the relative date slicer option?
It will be dynamic based on the Today date. Check out here - https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range#use-the-relative-...
I hope It will be helpful!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
78 | |
60 | |
51 |
User | Count |
---|---|
164 | |
84 | |
68 | |
68 | |
58 |