Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |