Timezone EST and UTC issues - Incremental Refresh and Power BI Service

[stribor45]

I had some issues with incremental refresh but after I solved that with help from this forum now I have whole another issues with timezones. I have power query which calls external API and since I am using incremental refresh I am using RangeStart and RangeEnd parameters for the datetime. How do I do this so that I send dates to API in UTC format (since Power Bi Service is in this format) but have my slider visual that works with EST dates?

[stribor45]

is there a way to see what API query is sent out by the service?

[lbendlin]

Yes, use the auditing of your data source to see the incoming queries.

[stribor45]

yeah i was aware of those. was mor einterested in tracking it on Power Bi Service side

[lbendlin]

That's not something Microsoft will want to show you.

[stribor45]

You know how you can see the DAX created by the PBI behind the visuals (Performance analyzer  -Start Recording). Something like that

[stribor45]

When I enter my parametars in Zulu time it automatically converts them?

[lbendlin]

The values you enter there do not matter at all.  The Power BI service will replace them during the incremental refresh according to the partition rules and based on UTC (Zulu).

[stribor45]

Can you share small example if possible?

[lbendlin]

Method 1

Solving DAX Time Zone Issue in Power BI - RADACAD

[stribor45]

Yes EDT my bad.  I always mix up between these. You are right it is EDT

[lbendlin]

Run everything in UTC, and then in your user interface subtract 4/24 from the datetime value to feed your slicer.

[rubayatyasmin]

Hi, @stribor45 

Try exploring Date timezone.SwitchZone and To local() in PQ. 

Refer: https://learn.microsoft.com/en-us/powerquery-m/datetimezone-switchzone

https://learn.microsoft.com/en-us/powerquery-m/datetimezone-tolocal

Alternatively you can use Dax to solve the Viz problem. 

Use Dateadd and -5, hour as the solution along with your UTC date time column name. 

[stribor45]

Can you make a small example if possible? It would be much appreciated.  I dont understand how in power query I can use UTC and then in Service somehow have slider that would use my EST time?

[lbendlin]

EST?  where are you located? Or did you mean EDT ?

[stribor45]

EST Toronto area

[lbendlin]

According to this Time Zones in Canada (timeanddate.com) you are currently on EDT.