cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VV_AG
New Member

Help with clustered column showing blank months

Hi All,

 

Im unable to show blank months correctly on a stacked column visual. The months show up correctly (albeit without the blank months) when I have "Show items with no data" unchecked (Top visual). However, if I select the option, the visual shows the months across 2020 and 2021 (bottom visual) instead of limiting the months to Aug 2020 - Jan 2021. 

 

 

I have "last 6 calendar months" as a slicer / filter and i need the visual to show Aug 2020 - Jan 2021 even when the data is blank. 

 

Can anyone help, please? Thanks in advance. MI.JPG

 

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @VV_AG 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

f1.png

 

Calendar(a calculated table):

Calendar = 
ADDCOLUMNS(
    CALENDAR(
        DATE(2020,1,1),
        DATE(2021,12,31)
    ),
    "Sort",
    YEAR([Date])*100+MONTH([Date]),
    "YearMonth",
    FORMAT([Date],"yyyy MMMM")
)

 

There is a relationship based on 'Date' column between two tables. You may create a measure like below.

Count = 
COALESCE(
   DISTINCTCOUNT('Table'[ID]),
   0
)

 

Result:

f2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-alq-msft
Community Support
Community Support

Hi, @VV_AG 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

f1.png

 

Calendar(a calculated table):

Calendar = 
ADDCOLUMNS(
    CALENDAR(
        DATE(2020,1,1),
        DATE(2021,12,31)
    ),
    "Sort",
    YEAR([Date])*100+MONTH([Date]),
    "YearMonth",
    FORMAT([Date],"yyyy MMMM")
)

 

There is a relationship based on 'Date' column between two tables. You may create a measure like below.

Count = 
COALESCE(
   DISTINCTCOUNT('Table'[ID]),
   0
)

 

Result:

f2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Brilliant, thanks for this. I can make it work with this approach. 

mahoneypat
Super User IV
Super User IV

Please confirm the YearMonth column used in the visual is from the same table (a Date/Calendar table) used in the visual.  If so, you should be able to just add +0 to your current measure expression (e.g., Sales = SUM(Table[Sales]) + 0.

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Portrek
Resolver II
Resolver II

Your problem can be solution with the anwser this post below:

 

https://community.powerbi.com/t5/Desktop/Show-previous-6-months-dynamic/m-p/75013#M31177

 

See you !

Hi, Thanks for replying but unfortunately the suggestion in the post hasnt worked for me. Ideally would have attached the pbix file butthe upload functionality is restricted on our network so unable to use any of the file hosting sites to upload and link t the post here. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors