cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

Accepted Solutions
Moderator v-caliao-msft
Moderator

Re: How to show/set slicer to current month

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

Re: How to show/set slicer to current month

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.

4 REPLIES 4
Moderator v-caliao-msft
Moderator

Re: How to show/set slicer to current month

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

Re: How to show/set slicer to current month

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.

 

Re: How to show/set slicer to current month

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.

Highlighted
Steve_Wheeler Established Member
Established Member

Re: How to show/set slicer to current month