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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DataNinja777
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.  

 

DataNinja777_0-1713232969588.png

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.  

DataNinja777_1-1713233132759.png

DataNinja777_2-1713233173294.png

 

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.  

1 ACCEPTED SOLUTION

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

DataNinja777_0-1713506119474.png

 

View solution in original post

4 REPLIES 4
Bmejia
Super User
Super User

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(
YEAR(table[Date])
,MONTH(Table[Date])
,DAY(Table[Date])
)

This is mine working using the past/future scenario

Bmejia_0-1713364876912.png

 

Bmejia
Super User
Super User

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.  

 

DataNinja777_0-1713311187052.png

 

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

DataNinja777_0-1713506119474.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.