Direct Query - WorkQueue / WorkQueueItems

[hsiddiq]

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

 )

[AnkitKukreja · Accepted solution]

Hi! @hsiddiq 

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

[Anonymous · Accepted solution]

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.

[AnkitKukreja · Accepted solution]

Hi! @hsiddiq 

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