Forum Discussion

huguest's avatar
huguest
Icon for Advocate II rankAdvocate II
8 years ago
Solved

Bar Chart by month with items with no data

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)...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi huguest,

     

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

     

    Regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
    huguest
    Icon for Advocate II rankAdvocate II
    8 years ago

    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's avatar
      v-yulgu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      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's avatar
        huguest
        Icon for Advocate II rankAdvocate II
        8 years ago

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