Forum Discussion
Direct Query - WorkQueue / WorkQueueItems
Hi team, if anyone can help me on this.
In my dashboard, I have workqueue and workqueueitem Dataverse tables conencted via a Direct Query.
Trying to Count (sum) of all the WorkQueueItemID
where Statecode = 2
CompeltedOn in the Last 60 mins.
Challenge,
The CompeltedOn (datetime) is showing in UTC, I have already got CurrentTimeStampSydney which I can use as Now() time.
in a directQuery how can I convert CompletedOn to AUST time and then check how many are in this status for last 60 mins?
I've tried
timeDifferent without any conversion, but failing to get the SUM/Count.
Not sure where my query is going wrong.
1. workqueueitem[compeltedon] >= Now() + Time(0,60,0)
2. TimeDifference (workqueueitem[compeltedon] - Now() + Time(0,60,0))
Hi hsiddiq ,
Thank you for reaching out. I understand you are trying to count the WorkQueueItemID where Statecode = 2 and CompletedOn is within the last 60 minutes, but you are facing challenges with the datetime conversion from UTC to AEST.
Firstly, I would like to ask if the suggestion provided by another user helped resolve your issue.
For counting calculation, you could try:
Processed_last60min = CALCULATE( COUNT('workqueueitem'[workqueueitemid]), 'workqueueitem'[statecode] = 2, 'workqueueitem'[completedon] >= (CurrentTimeStampSydney - TIME(1, 0, 0)) )If the issue persists, could you please provide some sample data and the expected results? This will help us better understand the problem and provide a more accurate solution.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi hsiddiq ,
Thank you for reaching out. I understand you are trying to count the WorkQueueItemID where Statecode = 2 and CompletedOn is within the last 60 minutes, but you are facing challenges with the datetime conversion from UTC to AEST.
Firstly, I would like to ask if the suggestion provided by another user helped resolve your issue.
For counting calculation, you could try:
Processed_last60min = CALCULATE( COUNT('workqueueitem'[workqueueitemid]), 'workqueueitem'[statecode] = 2, 'workqueueitem'[completedon] >= (CurrentTimeStampSydney - TIME(1, 0, 0)) )If the issue persists, could you please provide some sample data and the expected results? This will help us better understand the problem and provide a more accurate solution.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.