How to add month columns where data doesn't exist yet?

[PorterHaus]

I'm trying to recreate a column chart that has sales totals for every month in the current year. Since it's for the current year there isn't data yet for some of the months. How can I still have the months as columns and just empty? Right now it only shows month columns for months that have data.

Thank you in advance!

[Ashish_Mathur · Accepted solution]

Hi,

Drag months from the calendar table.  Thereafter just select 'Show columns when there is no data".  I will be able to offer specific help if you share the link from where i can download your file.

[Anonymous]

Hi @PorterHaus,

For your requirement, I'd like to suggest you to manually create these missing records and use union function to merge them as a new table.
After these steps, use new table as the source of chart.

Sample table: records from 2015 to today.

Test = ADDCOLUMNS(CALENDAR(DATE(2015,1,1),TODAY()),"Year",FORMAT([Date],"yyyy"),"Month",FORMAT([Date],"mmmm"),"Amount",RANDBETWEEN(1,10)*DAY([Date]))

Table 2 = 
var miss_date=IF(YEAR(LASTDATE(Test[Date])+1)=YEAR(LASTDATE(Test[Date])),LASTDATE(Test[Date])+1)
return
UNION(Test,ADDCOLUMNS(CALENDAR(miss_date,DATE(YEAR(miss_date),12,31)),"Year",FORMAT([Date],"yyyy"),"Month",FORMAT([Date],"mmmm"),"Amount",0))

Result:

Regards,

Xiaoxin Sheng