Forum Discussion

Heinrich's avatar
Heinrich
Post Partisan
1 year ago
Solved

DAX Script filter last x days

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 ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-veshwara-msft's avatar
    v-veshwara-msft
    1 year ago

    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.

lbendlin's avatar
lbendlin
Super User
1 year ago

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's avatar
Heinrich
Post Partisan
1 year ago

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

  • MFelix's avatar
    MFelix
    Super User
    1 year ago

    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's avatar
      Heinrich
      Post Partisan
      1 year ago

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

      Am I wrong?
      Regards
      Heinrich

       

       

      • MFelix's avatar
        MFelix
        Super User
        1 year ago

        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.

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    In your DAX use something like 

     

    [Date]>UTCNOW()-7