DAX Script filter last x days

Hi Heinrich ,

Apologies for the delayed response. I wanted to let you know that I tested the TODAY() logic, and it worked as expected. I'm wondering if you have been able to resolve the issue on your end. If not, please consider my response below.

 

1. Ensure that the Calendar table is correctly related to your fact table.

2. The filter logic in your original query has some redundancy. For example, this section:

AND(
			AND(
				AND('Calendar'[Date] >= TODAY() - 7, 'Calendar'[Date] < TODAY()),
				'Calendar'[Date] >= TODAY() - 7 
			),
			'Calendar'[Date] < TODAY()
		)
	)

can be simplified to:

'Calendar'[Date] >= TODAY() - 7 &&
'Calendar'[Date] < TODAY()

This ensures cleaner code and improves readability without altering the logic.

 

3. Combine all variable definitions under one DEFINE block:

DEFINE
    VAR __DS0FilterTable = ...
    VAR __DS0FilterTable2 = ...
    VAR __DS0FilterTable3 = ...
    VAR __DS0Core = ...
    VAR __DS0PrimaryWindowed = ...

4. Consider defining variables to store common values like TODAY() and TODAY()-7. This can clean up your code and reduce repetition.

VAR Last7Days = TODAY() - 7
VAR TodayDate = TODAY()
RETURN
FILTER(
'Calendar',
'Calendar'[Date] >= Last7Days && 'Calendar'[Date] < TodayDate
)

5. The KEEPFILTERS() function is only necessary when you want to preserve the context of filters, but it might be redundant if you’re already in the right context.

6. Ensure your data actually contains records for the last 7 days. If no data exists, your filter will return an empty result.

 

If you have received any error messages or encountered unexpected behavior, please share those details, that can help pinpoint the issue more precisely.

 

If you have found a solution or used a different approach that works, please share it with the community to help others.

If my response has been helpful, please consider marking it as Accepted Solution to assist others and a Kudos would always be appreciated.

Thank you.