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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kalyani
Helper I
Helper I

Current month default selection in Slicers, so that it will show all the visuals the current month

Month.JPG

 

I have a slicer like this and I have to set the selection default to the current month, so that all the other visuals will show only the current month data by default.

 

Can you please help me to do this

1 ACCEPTED SOLUTION

HI @kalyani,

 

If your calendar is based on current date, you can try to use slicer with relative mode to achieve default filter effect.

 

Sample:

Table = ADDCOLUMNS(CALENDAR(DATE(2015,1,1),TODAY()),"Amount",RANDBETWEEN(1,100)) 

10.PNG


Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

11 REPLIES 11
Rickmaurinus
Helper V
Helper V

To set a slicer to the current month by default you will have to use a workaround. Since the slicer will remember its choice and remain the same in each month, you need to introduce a value that can stay the same, but moves in the background. You can create an additional column in your calendar, that shows the value 'Default Month' or 'Current Month'. Then implement the logic based on the data of today. I wrote an article and recorded a video about this: 

 

https://gorilla.bi/power-bi/set-default-slicer-value/

 

--------------------------------------------------

@ me in replies or I'll lose your thread

 

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

 

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

How to add this formula to a date table that i created inside PBI and marked as the date table

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 

fraserjewing
Advocate I
Advocate I

This is a neat solution I use:

 

I came up with a neat solution for filtering on the 'Current Month'. Make sure that you have an entry in your Custom Date table for the Year Month in the format 'YYYY-MM'. Make sure you use a leading zero for the month part so it's always two digits like '2019-03'.

 

Once that's in place create this column using DAX:

 

Report Month = IF(MONTH(now()) = Month(DB_DIM_Dates[Date]) && YEAR(now()) = YEAR(DB_DIM_Dates[Date]), "Current Month", DB_DIM_Dates[Year-Month])

 

This will set each day for the current month to 'Current Month' and every other date to their respective Year-Month setting. Add this to a slicer, make it list view and sort descending and as 'Current Month' comes last alphanumerical it appears at the top of the list. 'Current Month' can always be your default value with others available to select. This means that when you subscribe it will happily roll over into a new month etc. You can modify this approach for any date range etc you are interested in.

 

Slicer.png

Thanks a lot for this solution!

 

I just used it, works the magic!

@fraserjewing 

 

Thank you for this tip!  It has saved me no end of problems.  I was trying all sorts of solutions, including seconday date tables etc, but this is solution is simple and affective.

 

Hopefully the Power BI team will add the ability to set slicer defaults, but for the time being this works great.  

 

Cheers 🍺

 

Mark

 

Anonymous
Not applicable

Hi All,

 

I have same requirment. Can this be done in simpler way like SSRS default value setup?

 

Veles
Advocate V
Advocate V

I don't think there's a way to do this other than selecting the month and republishing each time you need to update it. The slicer will default to whatever you had selected when you published.

 

You can get technical with DAX measures and have measures that only filter results based on the current month by default or by a month selected on a slicer.

 

I don't think it's really what you're trying to achieve but it will kind of fake what you want.

Hi,

Thanks for your reply.

 

In the Slicer, we have a option to select the current month with the date column. Just trying to do something similar to that using only the Month value.

 

Is that possible with the DAX measures? Can you give some idea on how to do that?

HI @kalyani,

 

If your calendar is based on current date, you can try to use slicer with relative mode to achieve default filter effect.

 

Sample:

Table = ADDCOLUMNS(CALENDAR(DATE(2015,1,1),TODAY()),"Amount",RANDBETWEEN(1,100)) 

10.PNG


Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi, thank you for this option. It worked if I switch my date slicer to a relative mode with This.

 

Earlier, i dont have an date column, but just created a date column with 1st date of every month and used this slicer and it worked. 

 

thank you for your suggestions.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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