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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rachelbi
Helper I
Helper I

How to show the blank month in bar chart within selected date range

I would like to show current month and upcoming three month data in the bar chart in power BI, how to show June's bar even it is null?

 

barchart.PNG

 

I have applied below filter but seems useless, many thanks in advanced.

Date Filter =

IF(

    COUNTROWS(

        FILTER(

            Opportunities,

            Opportunities[Est. close date] >= TODAY() &&

            Opportunities[Est. close date] <= EOMONTH(TODAY(), 3) &&

            YEAR(Opportunities[Est. close date]) = YEAR(TODAY())

        )

    ) > 0,

    1,

    0

)

1 ACCEPTED SOLUTION
danextian
Super User
Super User

Hi @rachelbi ,

 

 Are the periods in X axis from Opportunities table as well? Do those months exist in your data? If no to both, you cannot make a month appear if it doesn't exist in your data. What you can do is use a separate calendar table (which is the best practice by the way ), related that to your fact, use the columns from that calendar table and and show items with no data. Please see attached pbix.

danextian_0-1713169099816.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

6 REPLIES 6
danextian
Super User
Super User

Hi @rachelbi ,

 

 Are the periods in X axis from Opportunities table as well? Do those months exist in your data? If no to both, you cannot make a month appear if it doesn't exist in your data. What you can do is use a separate calendar table (which is the best practice by the way ), related that to your fact, use the columns from that calendar table and and show items with no data. Please see attached pbix.

danextian_0-1713169099816.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Thank you so much for both the solution and the clarification of my messy mindset 😀

Idrissshatila
Super User
Super User

Hello @rachelbi ,

 

try using this concept

measure name  = 
var mainMeasure = you put the dax of the main measure here
return
if( mainMeasure = blank(),0,mainMeasure)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




qqqqqwwwweeerrr
Super User
Super User

Hi @rachelbi 

 

if show data with not item does not work you can try below solution: 

 

for missing month you can try to this and intergate with you current approach this should be be solved: https://community.fabric.microsoft.com/t5/Desktop/To-show-all-legend-for-a-pie-chart/m-p/3833551#M12... and use this in one of your axis. If you still have confusion if not able to understand the logic you refer this video: https://youtu.be/WqE2As9RlgA?si=ApPGO3fZG_iwXSWu here in first part same missing months are shown in axis 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: https://www.youtube.com/@letssolveproblem

Regards

 

ryan_mayu
Super User
Super User

@rachelbi 

have you tried show items with no data?

https://learn.microsoft.com/uk-ua/power-bi/create-reports/desktop-show-items-no-data?wt.mc_id=DP-MVP...





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Yes I did .. 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.