DAX Script filter last x days

[Heinrich]

Hello

I am looking to have a dax script that filters last 7 days.

When I set the filter date on a visual to relative date = 7 days.

Then run performance analyzer the DAX query shows the exact day "from - to" and not the relative date

It shows not the relative days.
What is the DAX script for that.

Regards

Heinrich

[Heinrich]

Hello @v-veshwara-msft 

Hope you all had a great weekend.

Thanks. My belief was that the script was faulty. But I will try it and test it.
Regards
Heinrich

[lbendlin]

That's how the engine works. It takes your relative instructions and converts them into the absolute values as of the time the query is ran. 

[Heinrich]

Hello @lbendlin 
Thank you but I need a relative date because I will use it on Power Automate to export the data.
So the Dax should reflect relative date.
Regards
Heinrich

[lbendlin]

In your DAX use something like 

[Date]>UTCNOW()-7

[MFelix]

Hi @Heinrich ,

Adding to what @lbendlin  refers, being a relative date it will always pick up the dates from today or yesterday (if you select the include today) so basically  the DAX script is always from todays to todays - 7 days back.

If you want to have that in a Power Automate you must change the script to do that exact value:

Check the codes below:

I just replaced the Dates in this case DATE(2025, 4, 8) and DATE(2025, 4, 15) by TODAY() and TODAY()-7.

See the two codes below:

DEFINE VAR __DS0FilterTable = 
	FILTER(
		KEEPFILTERS(VALUES('Calendar'[Date])),
		AND(
			AND(
				AND('Calendar'[Date] >= DATE(2025, 4, 8), 'Calendar'[Date] < DATE(2025, 4, 15)),
				'Calendar'[Date] >= DATE(2025, 4, 8)
			),
			'Calendar'[Date] < DATE(2025, 4, 15)
		)
	)

EVALUATE
	SUMMARIZECOLUMNS(__DS0FilterTable, "Minimum_Year", IGNORE('Calendar'[Minimum Year]))


DEFINE VAR __DS0FilterTable = 
	FILTER(
		KEEPFILTERS(VALUES('Calendar'[Date])),
		AND(
			AND(
				AND('Calendar'[Date] >= DATE(2025, 4, 8), 'Calendar'[Date] < DATE(2025, 4, 15)),
				'Calendar'[Date] >= DATE(2025, 4, 8)
			),
			'Calendar'[Date] < DATE(2025, 4, 15)
		)
	)

EVALUATE
	SUMMARIZECOLUMNS(__DS0FilterTable, "Minimum_Year", IGNORE('Calendar'[Minimum Year]))

[Heinrich]

Hi @MFelix 
Thank you. Makes totally sense. 
Both codes are exactly the same

Am I wrong?
Regards
Heinrich

[MFelix]

My bad I copied the wrong code the second time:

DEFINE VAR __DS0FilterTable = 
	FILTER(
		KEEPFILTERS(VALUES('Calendar'[Date])),
		AND(
			AND(
				AND('Calendar'[Date] >= DATE(2025, 4, 8), 'Calendar'[Date] < DATE(2025, 4, 15)),
				'Calendar'[Date] >= DATE(2025, 4, 8)
			),
			'Calendar'[Date] < DATE(2025, 4, 15)
		)
	)

EVALUATE
	SUMMARIZECOLUMNS(__DS0FilterTable, "Minimum_Year", IGNORE('Calendar'[Minimum Year]))

DEFINE VAR __DS0FilterTable = 
	FILTER(
		KEEPFILTERS(VALUES('Calendar'[Date])),
		AND(
			AND(
				AND('Calendar'[Date] >= TODAY() - 7, 'Calendar'[Date] < TODAY()),
				'Calendar'[Date] >= TODAY() - 7 
			),
			'Calendar'[Date] < TODAY()
		)
	)

EVALUATE
	SUMMARIZECOLUMNS(__DS0FilterTable, "Minimum_Year", IGNORE('Calendar'[Minimum Year]))

Now is correct.

[Heinrich]

Hello @MFelix 
Hope you are doing great.
Thank you very much. Bud I should only use the second code. Right?

I tried following DAX

// DAX Query
DEFINE
	VAR __DS0FilterTable = 
		TREATAS({"CH"}, 'Assign Admin CQ Report'[Country])

	VAR __DS0FilterTable2 = 
		FILTER(
			KEEPFILTERS(VALUES('Alle-Agenturen'[CQN_Correlated_PowerBI])),
			NOT('Alle-Agenturen'[CQN_Correlated_PowerBI] IN {BLANK()})
		)

DEFINE VAR __DS0FilterTable3 = 
	FILTER(
		KEEPFILTERS(VALUES('Calendar'[Date])),
		AND(
			AND(
				AND('Calendar'[Date] >= TODAY() - 7, 'Calendar'[Date] < TODAY()),
				'Calendar'[Date] >= TODAY() - 7 
			),
			'Calendar'[Date] < TODAY()
		)
	)

	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			ROLLUPADDISSUBTOTAL(
				ROLLUPGROUP(
					'Alle-Agenturen'[CQN_Correlated_PowerBI],
					'fCallQueueAnalytics_SPO'[Date]
				), "IsGrandTotalRowTotal"
			),
			__DS0FilterTable,
			__DS0FilterTable2,
			__DS0FilterTable3,
			"CMCalls_SPO", 'fCallQueueAnalytics_SPO'[CMCalls_SPO],
			"CCalls_SPO", 'fCallQueueAnalytics_SPO'[CCalls_SPO],
			"CFWCalls_SPO", 'fCallQueueAnalytics_SPO'[CFWCalls_SPO],
			"CACalls_SPO", 'fCallQueueAnalytics_SPO'[CACalls_SPO],
			"CFWP_SPO", 'fCallQueueAnalytics_SPO'[CFWP_SPO]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(
			502,
			__DS0Core,
			[IsGrandTotalRowTotal],
			0,
			[CMCalls_SPO],
			0,
			'Alle-Agenturen'[CQN_Correlated_PowerBI],
			1,
			'fCallQueueAnalytics_SPO'[Date],
			1
		)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	[IsGrandTotalRowTotal] DESC,
	[CMCalls_SPO] DESC,
	'Alle-Agenturen'[CQN_Correlated_PowerBI],
	'fCallQueueAnalytics_SPO'[Date]

But it does not work.
Where is the error, do you have an idea

Regards
Heinrich

[v-veshwara-msft · Accepted solution]

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.

[Heinrich]

Hello @MFelix 
Absolutely no problem.

I used this code for Data filtering

	VAR __DS0FilterTable3 = 
		FILTER(
			KEEPFILTERS(VALUES('fCallQueueAnalytics_SPO'[Date])),
			AND(
				(
					'fCallQueueAnalytics_SPO'[Date] >= TODAY() - 14					
				),				
				'fCallQueueAnalytics_SPO'[Date] < TODAY()
			)
		)

It worked but think it misses something. 

Regards

Heinrich

[MFelix]

Hi @v-veshwara-msft ,

What is the problem that you are having with this code? Can you please give some additional information?

[Heinrich]

Hello @v-veshwara-msft 
Hope you are doing well.
The code of @MFelix is more elaborate and looking at mine I think I miss something.
Regards
Heinrich

[v-veshwara-msft]

Hi @Heinrich ,
Thank you for the update, and I am glad you were able to filter the data successfully using your version of the DAX code.

If your current setup is working as expected and you are getting the correct results, you can confidently continue using your solution.
Both your approach and the one shared earlier by @MFelix  are valid -- it mainly depends on how simple or complex your report requirements are.

If you notice any specific issues like missing records, unexpected totals, or any errors, feel free to share more details or screenshots so we can assist you further.

Also, if any response helped or you found a solution, it would be great if you could accept it as the solution to help others in the community.

Best regards,
Vinay.