cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
psmith-nhs-inc
Helper III
Helper III

How to show/set slicer to current month

OK.  I have two options, but I am looking for Curtain # 3. 

 

1) If I publish in July, my month slicer will only show Jan through July at the time of the publish.  I choose July in my slicer, publish, and that is what is in the service.  In August, July is still the selected slicer option, so the visuals are based on last month, not this month.  Only fix is to open the desktop, refresh, choose new month, republish.  This is a pain.

 

2) I can base a visual on a date based measure, so i can have a visual that always shows current month, whatever the current month is.  This is great, gives me what I want up front, but now isn't the visual stuck, and not sliceable anymore by month?

 

What would be ideal is to enable a slicer to self select the last month as default as part of the refresh, but be user selectable beyond that.  Is there anyway to pull that off?

 

Phil

 

 

2 ACCEPTED SOLUTIONS
v-caliao-msft
Microsoft
Microsoft

Hi @psmith-nhs-inc,

 

Currently, there is no such a option to automatically update to the latest month in slicer when the data is updated. Basically, the idea was to create a column that had a flag if it was the current month and current year and then filter on that instead of the actual month and year.

Create a calculated column using the DAX below.
LatestMonth = IF(FORMAT(MAX(Sales[DateID]),"YYYY-MM")=FORMAT(Sales[DateID],"YYYY-MM"),"Latest Month Data","History Data")

Capture.PNG

 

Regards,

Charlie Liao

View solution in original post

I made an improvement/Variation on your idea.  A 'Date' table is pretty common, most slicers will be based on that.

 

In your Date table create a calculated column such as:

 


CMisCurrentMonth = IF('Date'[MonthNumber] = Month(Today()),"CM",'Date'[Month])

 

Make sure you set the sort based on MonthNumber,

Then base your Month Slicer on CMisCurrentMonth that instead of Month.

The drawback is the current month will read CM instead of Oct, Nov, etc.  but if you choose CM before you publish, then as CM shifts to a new month, so will all of the selected data.  This allows you to have a month slicer, automatically choosing the current month when you go into the service, but still able to change the month once you are in the report.

You could do the same thing for the year.

View solution in original post

4 REPLIES 4
v-caliao-msft
Microsoft
Microsoft

Hi @psmith-nhs-inc,

 

Currently, there is no such a option to automatically update to the latest month in slicer when the data is updated. Basically, the idea was to create a column that had a flag if it was the current month and current year and then filter on that instead of the actual month and year.

Create a calculated column using the DAX below.
LatestMonth = IF(FORMAT(MAX(Sales[DateID]),"YYYY-MM")=FORMAT(Sales[DateID],"YYYY-MM"),"Latest Month Data","History Data")

Capture.PNG

 

Regards,

Charlie Liao

View solution in original post

The first time I read this, it was not makign sense to me.  I had to reread it, and look at the visual,  before I got it. 

 

Thank you.  That will work.

 

I made an improvement/Variation on your idea.  A 'Date' table is pretty common, most slicers will be based on that.

 

In your Date table create a calculated column such as:

 


CMisCurrentMonth = IF('Date'[MonthNumber] = Month(Today()),"CM",'Date'[Month])

 

Make sure you set the sort based on MonthNumber,

Then base your Month Slicer on CMisCurrentMonth that instead of Month.

The drawback is the current month will read CM instead of Oct, Nov, etc.  but if you choose CM before you publish, then as CM shifts to a new month, so will all of the selected data.  This allows you to have a month slicer, automatically choosing the current month when you go into the service, but still able to change the month once you are in the report.

You could do the same thing for the year.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors