Forum Discussion
Problem with filtering DAX Query - Wanting a dynamic date instead of static
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?
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')}))
)
1 Reply
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')}))
)