How to create a dynamic Date Table in Power BI with DirectQuery source (no scheduled refresh)?

Hi Data_Power_01 ,

Thank you for reaching out to the Microsoft Fabric Community Forum. Thank you amitchandak for your response.

You are correct that in DirectQuery mode, calculated tables such as CALENDAR() or Power Query transformations that reference DirectQuery sources create static materialized tables, which require scheduled refreshes. This goes against the goal of having a fully dynamic, real-time report.

To achieve a dynamic Date table that stays aligned with your DirectQuery source, without using Import mode or scheduled refresh, the recommended approach is as follows:

Create the Date table directly in your source system using a SQL view or native query:

Define a SQL view that calculates the date range dynamically from your fact table (for example, SalesData) and generates all dates between the minimum and maximum SalesDate. In SQL Server, you can do this with a suitable query.

WITH DateRange AS (
    SELECT MIN(SalesDate) AS StartDate, MAX(SalesDate) AS EndDate
    FROM SalesData
),
DateTable AS (
    SELECT DATEADD(DAY, number, StartDate) AS DateValue
    FROM DateRange
    JOIN master..spt_values ON type = 'P'
    WHERE DATEADD(DAY, number, StartDate) <= EndDate
)
SELECT DISTINCT DateValue FROM DateTable

Connect this view to Power BI as a separate table in DirectQuery mode. This approach ensures:

• The Date table automatically updates with changes in your fact table.
• It remains in DirectQuery mode, so no import or scheduled refresh is needed.
• You can use it in slicers or relationships to filter your main table.

Hope this helps. Please reach out for further assistance.

Thank you.