in between / less than slicer with month names instead of month numbers

[mostho]

I need a matrix visual with months as columns and the reader of the report should be able to select the range, for example january to march, or january to july. this works fine for me with an in between  / less the slicer using month of year as data field. (i have a facttable and a datetable with an active relationsship)

Now i was asked if its possible to have a default value (current month) for the slicer. I know that an in between/ less than slicer works only with numerical values or dates not with text.

With text (month names) i know how to do the current value as default value for the slicer with renaming the current month name as (e.g.) "default". But then there is only one column (the current month) in the matrix visual.

Any ideas how to get a possibiltiy for a default month in the slicer in the meaning of (january to current month) and having all the months in the matrix visual?

I would be fine with a numerical solution (in between/less than) with a default value and i would also be fine with a solution with mont names if the selection would be as "january to " interpreted.

[Anonymous]

Hi, @mostho 

Maybe you can add a month number column to make sure your date table has a column of month numbers

MonthNumber = MONTH([Date])

Use this column for slicer

Power BI doesn't currently support out-of-the-box dynamic default slicer values, but you can use bookmarks and buttons to emulate this behavior. Create a bookmark with the slicer set to the current month, and then use the button to navigate to this bookmark.

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quick

[Samarth_18]

Hi @mostho ,

Based on the description, this code could help:-

CurrentMonthSelection = 
VAR CurrentMonth = SELECTEDVALUE('Date'[Month of Year])
RETURN
    IF(
        CurrentMonth = "January", "January",
        IF(
            CurrentMonth = "February", "January to February",
            IF(
                CurrentMonth = "March", "January to March",
                IF(
                    CurrentMonth = "April", "January to April",
                    IF(
                        CurrentMonth = "May", "January to May",
                        IF(
                            CurrentMonth = "June", "January to June",
                            IF(
                                CurrentMonth = "July", "January to July",
                                IF(
                                    CurrentMonth = "August", "January to August",
                                    IF(
                                        CurrentMonth = "September", "January to September",
                                        IF(
                                            CurrentMonth = "October", "January to October",
                                            IF(
                                                CurrentMonth = "November", "January to November",
                                                IF(
                                                    CurrentMonth = "December", "January to December",
                                                    BLANK()
                                                )
                                            )
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )

[Ritaf1983]

Hi @mostho 
Can you show what do you mean?

[mostho]

Okay, lets do it. I have a fact table:

country	client	date	amount
usa	A	10.06.2024	10
canada	B	15.07.2024	14
argentina	C	02.02.2024	16
usa	B	02.02.2024	17

and a date table (this is only a part of the whole table):

Date	MonthNumber	MonthName	MonthNameCurrent
28.06.2024 00:00:00	6	June	June
29.06.2024 00:00:00	6	June	June
30.06.2024 00:00:00	6	June	June
01.07.2024 00:00:00	7	July	current
02.07.2024 00:00:00	7	July	current
03.07.2024 00:00:00	7	July	current
04.07.2024 00:00:00	7	July	current
05.07.2024 00:00:00	7	July	current

There is an active relationship via the date-colums.


If I use an in between/less than slicer with the MonthNumber and a matrix visual with country and client as rows, MonthName as column and amount as value I get this:


Which is ok, but it would be fine if the month (here: 6) would alway be the current month as preselected value.

If I use a text slicer with MonthNameCurrent I would have always the current month selected (thats fine) but then there is also only the current month in the matrix visual:

What i need:

the matrix visual like in the first picture and a possiblilty to select a month range like in the first picture but it should be able to have a current default month like in the second picture.

If the solution uses monthnumbers or monthnames doesnt matter.