Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
I have report with a Calendar table and an Expenses table. I used the Calendar table to create Year-to-Date, Quarter-to-Date, and Year-over-Year change measures. The Calendar table contains future dates so that I don't have to keep updating it as we add new Expense data. We are currently in Q2 of our fiscal year and the measures are working as intended for past dates. However, quarters 3 and 4 are showing up in the Matrix visual, in spite not having expenses since they are a future date. When I try to filter all FY25 dates from the visual, the matrix breaks because the measures are reliant on the filtered date column (see Image 1 below). When I try to filter the FY25 dates in the Data tab or Power Query, the Q3 and Q4 rows are still there (see Image 2). Any advice for getting rid of Q3 and Q4 (preferably in a way that does not require manual update whenever data for those quarters is input)?
Image 1: Filter Date on Visual Error
Image 2: Current View of the Matrix
Solved! Go to Solution.
I found the solution. If you right-click the row, there is an option to exclude.
Hi @smmcqueen ,
If the problem persists, please consider sharing sample data and formulas.
Best Regards,
Wearsky
I found the solution. If you right-click the row, there is an option to exclude.
Hi @smmcqueen
Have you tried modifying your measures to return blank for future dates?
Hidden Expense YTD =
IF(
MIN(datetable[date]) > Today(), BLANK(), [Your calculation])
Do this for all the measures, any rows that are completely Blank will be hidden by Power BI.
Alternatively you might be able to create a measure and apply a filter in the Filter pane, drag the measure in "Filters on this visual" then filter to Y.
Hide rows = IF(
MIN(datetable[date]) < Today(), "Y")
Hope this helps!
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
Thank you for the prompt response! I will try that and let you know if it works. Thank you!
Alternatively you might be able to create a measure and apply a filter in the Filter pane, drag the measure in "Filters on this visual" then filter to Y.
Hide rows = IF(
MIN(datetable[date]) < Today(), "Y")
This would only need to be done once vs once for each measure.
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
82 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |