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

6 REPLIES 6
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

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.

Hi, it seemed this solved the issue for most but I am showing it adds CM to every "February" every year in Date table. I am trying to have it only add CM value to the current month and current year. 

 

Any way to achieve that? I played with the formula but not sure how to accomplish it. 

 

Thank you!

Actually I found out its && that does the and inside the statement. 

 

Here is what I ended up with, it's perfect, can do a slicer toggle and then control Years / Months etc. with my other Slicer but can default to "Current Month".

 

Thank you for getting me there! 

 

You can use emoji's in your text in case anyone does not know that, see my example below. Looks great in Visuals

 

CurrentMonth =
IF([MonthofYear] = Month(Today()) && [Year] = Year(Today()), "📆 CURRENT MONTH", "📅 ALL DATES")

Anonymous
Not applicable

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.