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

The 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.

Reply
Tae
Frequent Visitor

Slicer to select current month as default

Hi,

 

I have created a dropdown date selection slicer so that my users can review the historical information.

image.png

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,

          Last Month = DATEVALUE(FORMAT(MAX('hierarchy'[MONTH]),"YYYY-MM"))

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.

image.png

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,

2020_01_02_10_29_44_HR_Dashboard_Prototype_Power_BI_Desktop.png

Or is there a better way to get the slicer to always select the latest month as a default?

 

Regards,

Tae

1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

Hi,

 

According to your description, I create a table to test:

61.png

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:

62.png

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:

63.png

4)choose column ‘Month Year’ of table ‘Slicer’ as a slicer:

64.png

5)choose the ‘Last Month’ and the result shows:

65.png

Here is my test pbix file:

pbix 

 

Best Regards,

Giotto Zhi

View solution in original post

8 REPLIES 8
rsandee5
Regular Visitor

can u share pbix file, its not found it says

Anonymous
Not applicable

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 

v-gizhi-msft
Community Support
Community Support

Hi,

 

According to your description, I create a table to test:

61.png

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:

62.png

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:

63.png

4)choose column ‘Month Year’ of table ‘Slicer’ as a slicer:

64.png

5)choose the ‘Last Month’ and the result shows:

65.png

Here is my test pbix file:

pbix 

 

Best Regards,

Giotto Zhi

Thanks Giotto,

This is exactly what I wanted to know!

 

amitchandak
Super User
Super User

Relative option on date slicer can help. That will change the display

.Screenshot 2019-12-24 19.21.20.png

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
mwimberger
Resolver II
Resolver II

 @Tae 

 

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

 

Latest available Period.png

 

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.

SivaMani
Resident Rockstar
Resident Rockstar

@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!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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