How to create a data or calendar table within Dremio?

I’m looking to have a “Calendar” table created where it will give me the date between ranges ,say the past 5 years through today, or the past 5 years to the next 5 years. I’ve done something similar from data from a dataset but I need to ensure this table has dates that do not exist in the dataset I am querying from.

For example, here is what I’m doing today:

    DISTINCT Date_YearMonthDay, 
    CONCAT(TO_CHAR(Date_YearMonthDay, 'yyyy'), '-', 
    TO_CHAR(Date_YearMonthDay, 'MM')) as CalYearMonth, 
    TO_CHAR(Date_YearMonthDay, 'yyyy') as CalYear, 
    TO_CHAR(Date_YearMonthDay, 'MON') as CalMonth
        SELECT DISTINCT CreatedDate as Date_YearMonthDay FROM TableWithDates
ORDER BY DATE_YearMonthDay desc 

However, I’d like Dremio to provide me with the TableWithDates based on the range I’m looking for. Similar to what is posted here:

Sure I can do this the physical database/datasource, but I’d rather do it in Dremio for a variety of reasons.


In the example you have provided, SQL Server has table called Calendar which they run a loop to do this. Dremio does not have one. Is your ask to have a table like SQL server?


Well, I’d like to create this somehow, whether its a loop process, or something like in DAX where you can do CALENDARAUTO()

I am using this currently in Power BI and would like Dremio to perform this for me as a VDS so I can re-use in multiple places, rather than siloed in Power BI Model.