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:

    SELECT 
    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
FROM 
    (
        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: https://stackoverflow.com/questions/5635594/how-to-create-a-calendar-table-for-100-years-in-sql.

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

@rwetzeler

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?

Thanks
Bali

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.