RELATIVE DATE FILTER

[djm7]

I have a table report with a relative date filter where I want to see orders that are in the previous 42 days re confirmed delivery date

I have turned this report into a query which I then use to create a csv file using 'Run a Query Against a Dataset'

I just want to make sure that the filter in the query is still a relative date filter and not hard coded to the current date.

VAR __SQDS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('Sales order'[Confirmed delivery date])),
AND(
'Sales order'[Confirmed delivery date] >= DATE(2025,1,10),
'Sales order'[Confirmed delivery date] < DATE(2025, 2, 20)
)
)

[djm7]

thanks - kind of what I expected as it looked hardcoded and not variable.

[FarhanJeelani · Accepted solution]

Hi @djm7 ,

Your DAX filter is currently hardcoded to the dates January 10, 2025 – February 20, 2025. This means it will not dynamically update based on the current date like the relative date filter in your Power BI report.

To ensure the filter remains dynamic (always showing the last 42 days including today), modify your DAX to use TODAY() instead of fixed dates:

VAR __SQDS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('Sales order'[Confirmed delivery date])),
'Sales order'[Confirmed delivery date] >= TODAY() - 42 &&
'Sales order'[Confirmed delivery date] <= TODAY()
)

This will dynamically adjust the date range based on the current date when the query runs, keeping it consistent with the relative date filter in Power BI.

Please mark this post as solution if it helps yopu. Appreciate Kudos.