Frequently we get asked about how to fill in missing dates. To start, you can see here in my base data that I am missing the 1st, 8th, 15th, 22nd, and 29th. Some will have data like this where it is 1 row per day as I do below, others will have more line item type data. Regardless, we know there are missing dates and the solution is almost identical.
Create a date dimension table if you dont have one in your warehouse already. My preferred method is via custom SQL. Here I use a combination of snowflakes Generator function and dateAdd from an end date. You could make this even more dynamic by using current_date() and backfill the last 3 years if you wanted to. Ill leave the SQL for other warehouses in the comments below. I should also note that you could upload a CSV of the full date list or even use an input table. In this use case it is less necessary for the dimension table to be dynamic but as pointed out above, there are other use cases that would backfill the last 3 years from today in which case the SQL is best.
Once we have our dimension table, we can Add via lookup from our base data. To do this, right click the date column and select “Add Column Via Lookup”. This is where things vary slightly depending on the format of your data. Since my base data has 1 row per data I don’t need an aggregate, but if your data is more granular, feel free to set aggregate to Sum in the lookup helper modal.
Now we see that we have a table with rows for all the dates, including the ones missing from the original! To complete this we can simply wrap the lookup in the formula bar with the ZN() function aka Zero if Null.
If this was helpful but you still need assistance or want clarification around any of these steps, let us know!