Problem with filtering DAX Query - Wanting a dynamic date instead of static

[SandraBudding]

Morning,

I'm trying to make my DAX query dynamic for getting date from SQL to a Microsoft Lists using Power Automate.

The part that important from my DAX query is as follows:

                VAR __DS0FilterTable2 =

                               FILTER(

                                               KEEPFILTERS(VALUES('OHW'[CalcDate])),

                                               AND('OHW'[CalcDate] >= DATE(2024, 1, 8), 'OHW'[CalcDate] < DATE(2024, 1, 9))

                               )

What I would like to get is that 'OHW'[CalcDate] always takes Today()-1 or Yesterday. So yesterday it was 2024,1,8 > 2024,1,9 but tomorrow I would like it to be 2024,1,9 > 2024,1,10. From what I found online I already tried to use:

VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('OHW'[CalcDate])),
AND('OHW'[CalcDate] >= today()-1)
)

but that's giving me errors in Power automate: Error - Action 'Run_a_query_against_a_dataset' failed.

Anyone who knows how to solve this problem?

[SandraBudding · Accepted solution]

Already found the solution myself. For people who are looking to solve this in the future:

VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('OHW'[CalcDate])),
AND('OHW'[CalcDate] >= DATE(@{formatDateTime(utcNow(), 'yyyy,MM,dd')}), 'OHW'[CalcDate] < DATE(@{formatDateTime(addDays(utcNow(),1), 'yyyy,MM,dd')}))
)