Display missing data as 0 in line chart

[bleow]

My transformed (unpivoted) data looks like this:

ID	Activity	Date	Month-Year
001	OrangeActivity	11/11/2020	Nov 2020
001	BlueActivity	13/11/2020	Nov 2020
003	OrangeActivity	4/12/2020	Dec 2020
006	BlueActivity	4/1/2021	Jan 2021

Setting Month-Year as x-axis and Count of ID as y-axis (and applying a hidden index sort), I get this:

The issue comes for Month-Years that don't have an entry in the data, e.g. Dec 2020 for blue activity. 

I tried "Show items with no data", the CALCULATE(... +0) trick and the if 0 then 0 trick, but all didn't seem to work.

My last resort I can think of is to generate a calendar table, do a left join, and replace blank values with 0, but that will make my table extremely huge (the date can go back decades), and also means it will keep growing everyday. Is there a better way?

[amitchandak · Accepted solution]

@bleow , That trick will work if you have a separate date table and use month year from there. 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[bleow]

Thank you so much, this did the trick. I still need a separate date table with all the days (over 500k rows), but with only three columns the processing time is much more manageable than my last resort.

For anyone reading this and requiring the same solution:

//new DAX table
DateRange = 
CALENDAR(
      min(Table[Date])
    , max(Table[Date])
)

//new columns
MonthYear = FORMAT([Date], "mmm yyyy")
Index Month-Year = RANKX('5 DateRange', DATE(Year([Date]), MONTH([Date]), "1"), DATE(Year([Date]), MONTH([Date]), "1"), ASC, Dense)

//Select MonthYear in the right Field pane, go to Column tools > Sort by column > Index Month-Year.
//Set MonthYear as x-axis. MonthYear should be sorted by time-series now instead of alphabetically.