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
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
Employee
Employee

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
Employee
Employee

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
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.