Power BI Charting Question

[espo4life]

I have a situation where I want to show every month for this year on the X-axis, but if there is no data for a month, just leaves it blank (no 0 points or anything). Below is the chart I want, but I had to add 0 values to the table for Jan 2020 (circled in red).

If there is no data in the "weather" table for Jan 2020, I want Jan 2020 to be on the x-axis, but it blank on the chart.

[mahoneypat]

Two potential suggestions 

1. Under the formatting options for the chart, fix the X-axis to your desired start date.

2. Add a constant measure (e.g., Constant = 1) and add that to your chart.  Color it so it is not visible.  It would show in your legend, of course.  Not a great option, but a potential quick fix.  You could even hide that part of the legend with a little white rectangle.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[az38]

Hi @espo4life 

first, create a calendar table like

Calendar = CALENDARAUTO()

or

Calendar = CALENDAR(date(2020,1,1), date(2020,12,31) )

then create a relationships between calendar table and your fact table by [Date] field and put Calendar[Date] field to x-axis of chart

[espo4life]

Thank you @az38  but it did not seem to solve my problem. Here is a samle pbix file with the table and my Date dimention and your Calendar one.

https://www.dropbox.com/s/23s68yqo7d782mq/weather.pbix?dl=0

Please look at it and see if/what I am missing. Thank you so much in advance!

[az38]

@espo4life 

you also can create a colum in calendar table

AvgTemperature = 
var _relatedTemperature = RELATED(WeatherHistory[AvgTemperature])
RETURN
IF(ISBLANK(_relatedTemperature); 0; _relatedTemperature)

and put Calendar[Date] to X axis and new column to Y