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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Sambath
Frequent Visitor

Slicer with last 12 complete months and dynamic default to the latest complete month

Hello Power BI Gurus,

 

I'm looking for a simple solution to have a 12 month slicer that dynamically defaults to the latest complete month. For example, while we are in Apr 2023, I wanted to have a slicer with the last 12 complete months: Mar 2023, Feb 2023, ..., Apr 2022 and it defaults to the latest complete month - Mar 2023 in this example. I've come across a few posts that suggest how to make the latest month/current month/today as default but can't figure out how to have a list of months on top of it. 

Note: I have a calendar table in my report. 

See a screenshot of a similar slicer that I built in another BI platform. 

Sambath_0-1681366122737.png

 

Thank heaps in advance for any suggestsions. 

Regards 

 

3 REPLIES 3
danextian
Super User
Super User

Hi @Sambath ,

 

Your second  requirement is currently not supported. I've read somewhere in the internet that this may be a thing soon but I can't find that in the current Power BI roadmap. Alternatively, you can add a calculated column that indicates whether period is the latest one.

 

For your first requirement, you have to create a calculted column to filter the available periods to show only the last 12 ones.  Here's a sample formula

Number of Months from Current = 
VAR __months =
    DATEDIFF ( Dates[Date], MAX ( Dates[Date] ), MONTH ) + 1
RETURN
    __months <= 12

 

Please  see attached pbix for your reference.

 

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
ovde
Resolver II
Resolver II

Hi Sambath,

You probably want to apply a relative date filter to your slicer using the date field from your calendar. This wil show al list of the last twelve completed months. Is far as I'm aware you cannot set a dynamic default, but with sort you can set the latest month to show up on top.

ovde_0-1681366988280.png

 

Hope this helps!

Sambath
Frequent Visitor

Thank for the prompt response but I wanted these two features: default to the latest complete month and a list of the last 12 months for users to select. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.