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
ROYMMM
Advocate I
Advocate I

Hide future months in matrix

Hi,

 

I have a report with a matrix showing the availability of services. Every column represents a list of availability percentages of the respective services for a specific month. The matrix should only show the months of the current year. (this is set in the filter panel) In the screenshot all months for the current year are shown and I only want the past months to be shown. Can someone help me?

 Screenshot_1.pngScreenshot_2.png

 

Thanks!

5 REPLIES 5
v-shex-msft
Community Support
Community Support

HI @ROYMMM,

You can add if statements to your measure formula to check date and skip calculations on records that greater than current date. If full of row/column does not contain valid values(no blank value), they will be hidden by visual itself.

If you confused about coding formula, can you please share some dummy data to test? It is hard to clearly your table structure form matrix snapshot.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
sturlaws
Resident Rockstar
Resident Rockstar

Hi @ROYMMM,

 

I assume you have some sort of date table. In your date table, do you have a column for the first date of the month? If not, create it like this

LastDateOfMonth = endofmonth('Date table'[Date])


Then create a new column like this

IsFutureMonth = if(today()>LastDayOfMonth;1;0)


Now for you matrix visual, add IsFutureMonth to the filter pane, and set the filter to IsFutureMonth=0.

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

If this calculated column is used as.a filter less than 0, it doesn't show anything. If it's equal to 0, it shows only the current month. If it's greater than 0, it shows all months, including future ones.

 

I have tried your "solution" on two matrices, with and without YOY columns, and the result is the same.

parry2k
Super User
Super User

@ROYMMM you should add a calendar dimension in your model which is a best practice to for any time intelligence based reporting, add month offset column in that table with datediff and month interval like below,

 

Month Offset = 
DATEDIFF ( Calendar[Date], TODAY(), MONTH )

 

Above column will make current month as zero (as of today feb 11, 2020), Feb 2020 will be 0 and Jan 2020 will be -1 and so on, and future months will be positive values, March 2020 will be 1, April 2020 will be 2 etc.

 

Now you can use this Month offset filter as page level or report level filter,  where month offset is less than equal to 0, and it will alway show current and past month and you can change this filter value as per your need.

 

Or with 2nd trick, using calendar dimension, as relative date filtering link here

 

All in all, you need calendar/date dimension in your model.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

The question was about how to remove future months from a matrix, not how to get the current one.

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.