Streaming Analytics Dataset

[jgarciabu]

Not exactly related to PowerBI, but I output a streaming dataset to PowerBI web service and I'd like to tweak it to gather data from a certain point in time. Here is my current query:

SELECT
dateadd(day,-1, system.Timestamp) as StartTime, system.Timestamp as EndTime, sum(ordertotal) as DailyCash
INTO
[Dailycashoutput]
FROM
[Dailycash]
GROUP BY TumblingWindow(Duration(day,1), Offset(hour, -7))

I'd like to gather data from my Event Hub source that reflects all data in a 24 hour window in the Pacific time zone. I'm unclear on how this should be done. I think Event Hubs are on UTC which is why I set the offset for the tumbling window to -7 hours, but do I have to write the query at 12AM pacific time and start running it at the exact time to capture the data I need or is there another way to do this?

[Anonymous]

Hi @jgarciabu,

We have less control about converting Time Zone on Power BI side, you may need to modify StartTime to dateadd(hour,-7,dateadd(day,-1, system.Timestamp)) and modify EndTime to dateadd(hour,-7,system.Timestamp), reference: https://msdn.microsoft.com/en-us/library/azure/dn834997.aspx .

For more details about how to convert UTC to Pacific in your query, I would recommend you post the question in the Azure Stream Analytics forum to get dedicated support.

Thanks,
Lydia Zhang