Slicer to select current month as default

[Tae]

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,

          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.

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

[Anonymous]

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 · Accepted 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:

pbix 

Best Regards,

Giotto Zhi

[Tae]

Thanks Giotto,

This is exactly what I wanted to know!

[amitchandak]

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

.

[mwimberger]

 @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

Maybe this helps you in a simpler, easier way. 

Cheers and Happy New Year!

Manfred

[Tae]

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]

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