Date of last refresh is in PST and Today date Is picking in UTC Date

Power Query Data Setup
1

Hi,

I have a report set to refresh every 30 minutes in PST time. and I show the last refresh date and time in PST on the report, and the report and displays correctly.

I have also set my dataset to always show data for today only. For this, I have a column in my date table that shows 1, if current date is today, and 0 date is not current date.

image
image1819Ă—125 8.63 KB

I set this up in power query, using following step :

Table.AddColumn(#"Removed Other Columns", "Is Today", each if [Date] = 
Date.From(
       DateTimeZone.SwitchZone(
               DateTimeZone.UtcNow(),-8
                )
        )then 1 else 0
)

here I used : DateTimeZone.UtcNow(),-8, so that the utc date changes to todays in the last few hrs of the day,

This doesn’t seem to be working properly.

For the entire day, until evening, report works fine, but in the last few hrs of the day, today date changes to next day
like this
image

the last refresh date and time still shows PST date and time of refresh.

This situation means that my report gives no results. as the Today date changes (to next day), and the no data exists in data source for that date.

Please can you guide me on this, Looks like i am missing something.

2

Hi @jsodhi,

Maybe create a validation table and call the date/time from there?
Test with this.

let
    Source = Table.FromRecords(
        {
            [ Name = "Current TimeZone", Value = TimeZone.Current ],
            [ Name = "UTC time", Value = DateTimeZone.FixedUtcNow() ],
            [ Name = "UTC -8 Hrs", Value = DateTimeZone.SwitchZone( DateTimeZone.FixedUtcNow(), -8 ) ]
        },  type table [Name = text, Value = any]
    )
in 
    Source
2 Likes
3

Hey, Melissa,
Thanks for your response.

Sure. I will try that today and get back to you.

Regards,

4

Hello @jsodhi ,

Did the response from @Melissa helped solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark her answer as the SOLUTION.

Thank you

5

Hello @jsodhi ,

Just following up if the response above help you solve your inquiry?

We’ve noticed that no response was received from you on the post above. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

6

Hi @jsodhi ,

Due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,.

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!

7

Hey @Melissa

I did eventually got to implement this in the report.
So it works in a way that instead of calendar table, i use slicer using date from this table (UTC-8hrs) and I also use this table to filter all tables in my data model.

and that works. Now i always show todays data in the report and it remains in UTC time. The only challenge is that users dont have an option to select any previous dates.

do you have any recommendation to achieve this, or ideas to point me in correct direction

Regards.