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
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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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