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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Super User
Super User

Filtering out future dates while not suppressing zeros in visual

Hi Power BI community,


I have a bar chart visual, and some yyyy-mm have zero/ blank values, but I'd like the bar chart visual to show all the yyyy-mm without skipping (suppressing) zero/ blank for yyyy-mm fields on y-axis so that I can see the whole picture of account balances over time (i.e., not only the periods when there were balances).  However, when I ticked "Show items with no data", all the dates including the future dates in the calendar table is displayed in the visual.  



In order to remove the future dates, I've created a calculated column in the calendar table, and tried to filter out the future dates in the visual through the filter pane.  However, I cannot select Apply filter to activate this filtering.  




I'd greatly appreciate it if you could let me know how I can get full list of yyyy-mm field in the y-axis in the bar chart while filtering out future dates.  


Thank you for your help in advance.  


This issue was resolved by filtering out future dates in the calculated calendar table.  



View solution in original post

Solution Supplier
Solution Supplier

I recreated your scenario and I do not see an issue.  The only thing that i could think of is that there is no relationship between the "date" table and your "data" date table.  If there is then I would check that both dates are formated the same way, cause i had run with this issue before where even though the column looks like a date and there is a relationship. When you filter the data it does not take any affect.

This is how i would tested,  create a new column that formats your current date to just Y/M/D then create a relationship a new relationship using this new column.
Calendar Format = DATE(

This is mine working using the past/future scenario



Solution Supplier
Solution Supplier

The YYYY-MM is a text column not a date column. So you can not use relative date on it.  You would need to add the date column that the YYYY-MM has a relationship to in the filter pane.  The reason the YYYY-MM Column is gray out is cause you need to enter a criteria since is a text column.  

Hi @Bmejia 

Thanks for your comment, but I am not using yyyy-mm to filter the visual, and I am trying to filter the visual using "Past date" field which is a calculated column in 445 Calendar table. Despite the selection of "Past date" the filtering is not behaving in the way intended.  




This issue was resolved by filtering out future dates in the calculated calendar table.  



Helpful resources


Fabric certifications survey

Certification feedback opportunity for the community.