topic Re: Direct Query - WorkQueue / WorkQueueItems in Power Query

Direct Query - WorkQueue / WorkQueueItems

hsiddiq — Fri, 06 Sep 2024 01:48:50 GMT

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))

Processed_last60min =

CALCULATE(

COUNT('workqueueitem'[workqueueitemid]),

workqueueitem[statecode] = 2,

//createdOn in the last 60 mins

)

Re: Direct Query - WorkQueue / WorkQueueItems

AnkitKukreja — Fri, 06 Sep 2024 10:50:39 GMT

Hi! hsiddiq

Try this CompletedOn = Table1[CompletedOn] + TIME(10, 0, 0)

Re: Direct Query - WorkQueue / WorkQueueItems

Anonymous — Wed, 11 Sep 2024 08:19:47 GMT

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.