cancel
Showing results for
Did you mean:
Frequent Visitor

## 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?

Thanks!

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

Super User III

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

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.

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors