Help with clustered column showing blank months

[VV_AG]

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. 

[v-alq-msft · Accepted solution]

Hi, @VV_AG 

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

Table:

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:

Best Regards

Allan

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

[VV_AG]

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

[mahoneypat]

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

[Portrek]

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 !

[VV_AG]

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.