Thank you Tushar. Your method works with my Excel dataset.
By any chance do you know how to solve the similar issue should the similar dataset comes from Tableau Online/Server?
Good question. Normally, I do all these things prior publishing it to the server. There a couple of things you can do now. Create a date table that has all date values. Open your workbook, add this as a new data source and you can blend on dates using these two data sources. Make sure use date data source connection as a primary.