Skip to main content
cancel
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.

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

This is mine working using the past/future scenario

Bmejia_0-1713364876912.png

 

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

 

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.