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

[Data_Power_01]

Hi all,

I’m working on a Power BI report using DirectQuery mode, and I’m looking to create a separate Date table that can be used to filter a DirectQuery table dynamically—without needing a scheduled refresh or manual updates.

Scenario:

I have a table (let's say SalesData) connected via DirectQuery, which contains a date column named SalesDate.

This column has data for about 4 months.

I want to create a separate Date table to be used in a slicer, which can pass the selected date filter to the SalesData table.

The key requirement is: the Date table should dynamically reflect the date range available in the DirectQuery table.

Problem:

I understand that in DirectQuery mode, calculated tables (like CALENDAR() or CALENDARAUTO()) are not supported because they require a data refresh and materialize at design time.

I also tried this approach:

CALENDAR(MIN(SalesData[SalesDate]), MAX(SalesData[SalesDate]))

But it doesn’t work in DirectQuery because MIN() and MAX() on DirectQuery columns require a data refresh, which I’m trying to avoid.

Similarly, in Power Query, if I reference the DirectQuery table to extract distinct dates, it doesn’t stay dynamic unless I refresh the dataset manually or on a schedule—which again defeats the purpose of keeping it real-time via DirectQuery.

My Requirement:

I want a separate Date table (not a hardcoded static list) that:

Reflects the date range of the underlying DirectQuery table.

Can be used to filter the main DirectQuery table using relationships or DAX measures.

Updates automatically as the DirectQuery source data changes, without requiring a scheduled refresh.

Questions:

Is there any way to build a Date table that stays in sync with the DirectQuery data, without switching to Import or triggering dataset refreshes?

Is there a way to generate the Date table directly from the DirectQuery source (like using SQL view or native query) so that it remains dynamic?

•  

Would a composite model (Import + DirectQuery) be a better approach for this scenario?

Any suggestions, design patterns, or best practices would be greatly appreciated. Thanks

[v-tsaipranay]

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.

[amitchandak]

@Data_Power_01 ,

Is there any way to build a Date table that stays in sync with the DirectQuery data, without switching to Import or triggering dataset refreshes?

Import mode is best. You can use mixed mode, to have table in DAX. But that will require a refresh of date table. Create a date table in such a way that 1 refresh per day should be fine 

Is there a way to generate the Date table directly from the DirectQuery source (like using SQL view or native query) so that it remains dynamic?

You can create table at source , you can make it dynamic there as per data load in that source 
https://medium.com/@amitchandak/power-bi-direct-query-date-table-in-sql-server-b5f4fe0f6d3d
Best it refresh daily with new dates 

The same can be done with DAX calculated tables. You can usethe  start year of Min date and End of Max date and refresh it once in day.