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

[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.