Bar Chart by month with items with no data

[huguest]

Hello,

I am trying to create a stacked bar chart where the x-axix is a date.  I want to show a count by month over the last 12 calendar months, and show the months where there is no data (count = 0) on the axis.  Here is a sample data set.

Date	Site
01Dec16	C
01Mar17	A
01Mar17	B
01Jun17	A
01Jul17	A
01Sep17	A
01Oct17	A
01Nov17	B
01Jan18	C

I want the stacked bar chart to have a x-axis that goes from January 2017 to December 2017, and show the months where the count is 0 (Jan17, Feb17, Apr17, May17, Aug17 and Dec17). 

I am able to get a chart that shows March 2017 to November 2017, but does show Jan, Feb and Dec. [Date in "Date" format; Visual Level Filter - Date is in the last 12 calendar month]

I am also able to get one that shows January 2016 to December 2018. [Date in "Date Hierarchy" format; Visual Level Filter - Date is in the last 12 calendar month].

But I am unable to get one that starts in January 2017 and ends in December 2017.

Any help would be appreciated!

Thank you,

Hugues.

[v-yulgu-msft]

Hi @huguest,

Create a calendar table which lists unique date values. And you should establish a one to many relationship from 'dimDate' to 'Fact date' based on [date] column.

dimDate =
FILTER (
    CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2018, 1, 1 ) ),
    DAY ( [Date] ) = 1
)

Then, create the stacked column chart, please pay attention to the hilighted part in below screenshots.

Best regards,

Yuliana Gu

[huguest]

Hello, thank you for your suggestion.  It did work to add the leading balnk months to my chart (i.e. january and February), but December is still not showing.

[v-yulgu-msft]

Hi @huguest,

Based on the steps mentioned in my original post, please change the axis type to "Categorical". Then, change [Date] field to "Hierarchy".

Best regards,

Yuliana Gu

[huguest]

Thank you, the solution is working (for now).  I have under the impressiont that it will no longer work when I get into February and have to report on Feb2017 to Jan2018.  As the x-axis shows only themonth an not the year, I will end up with Jan2018 as my first bar, then Feb through Dec2017...

[v-yulgu-msft · Accepted solution]

Hi @huguest,

In that case, you can choose "months" rather than "calendar months" as shown in below screenshot.

Regards,

Yuliana Gu