Forum Discussion
Power automate subscription not processing correct dax query
I have a flow set-up that essentially will send out a report to a client running a dax query that it pulls from sql server. I know that the flow works because the client receives the files. The query is set up to pull the prior day's transactions. It works completely fine according to the client, and they do successfully receive the data, but for some reason it never pulls data from the weekend days (Saturday/Sunday). I tried modifying the dax date filter logic to pull TODAY() - 3 days to see if the Monday pulls would cover Sat/Sun, but still to no avail.
Anyone ever run into an issue like this? When I test the dax pulls myself in the appropriate report, it generates all the data fine.
Likely causes are timing/UTC, not DAX.
Quick fixes (pick one):
Pass the date from Flow (best)
In Power Automate:
prevDate = formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')DAX query:
EVALUATE
VAR PrevDate = DATEVALUE("@{variables('prevDate')}")
RETURN
CALCULATETABLE(
YourTableOrSummary,
'Date'[Date] = PrevDate
)
This avoids TODAY()/NOW() timezone issues.If you must use DAX dates, use UTC:
VAR PrevDate = DATEVALUE( FORMAT( UTCNOW(), "yyyy-MM-dd" ) ) - 1
RETURN CALCULATETABLE( … , 'Date'[Date] = PrevDate )
Make Flow run after refresh:Ensure the Recurrence trigger includes Sat/Sun.
If the model refreshes on a schedule, trigger a dataset refresh first, then “Wait” (e.g., 15–30 min) or poll the refresh status, then run the DAX query.
Also check:
No incremental refresh/window excludes weekends.
The dataset actually has weekend rows by the time Flow runs.
You aren’t querying a DirectQuery/SQL view that omits weekends.
Gateway/tenant/region time zone mismatch.
In short: pass the exact date from Flow (UTC) and schedule the query after the dataset refresh.
Hi vegasde100,
Just checking in! We noticed you haven’t responded yet, and we want to make sure your question gets fully resolved.
Let us know if you need any additional support.
Thanks again for contributing to the Microsoft Fabric Community!
4 Replies
Likely causes are timing/UTC, not DAX.
Quick fixes (pick one):
Pass the date from Flow (best)
In Power Automate:
prevDate = formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')DAX query:
EVALUATE
VAR PrevDate = DATEVALUE("@{variables('prevDate')}")
RETURN
CALCULATETABLE(
YourTableOrSummary,
'Date'[Date] = PrevDate
)
This avoids TODAY()/NOW() timezone issues.If you must use DAX dates, use UTC:
VAR PrevDate = DATEVALUE( FORMAT( UTCNOW(), "yyyy-MM-dd" ) ) - 1
RETURN CALCULATETABLE( … , 'Date'[Date] = PrevDate )
Make Flow run after refresh:Ensure the Recurrence trigger includes Sat/Sun.
If the model refreshes on a schedule, trigger a dataset refresh first, then “Wait” (e.g., 15–30 min) or poll the refresh status, then run the DAX query.
Also check:
No incremental refresh/window excludes weekends.
The dataset actually has weekend rows by the time Flow runs.
You aren’t querying a DirectQuery/SQL view that omits weekends.
Gateway/tenant/region time zone mismatch.
In short: pass the exact date from Flow (UTC) and schedule the query after the dataset refresh.
Hi vegasde100,
Thankyou VahidDM for your reply on the issue.
I'm glad to hear that your query was resolved! If the response provided by the community member addressed your concern, kindly confirm.
If you have any further questions, feel free to reach out!
Thank you.Hi vegasde100,
Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.Thank you.
Hi vegasde100,
Just checking in! We noticed you haven’t responded yet, and we want to make sure your question gets fully resolved.
Let us know if you need any additional support.
Thanks again for contributing to the Microsoft Fabric Community!