Last Refresh Date in PowerBi

[PPStar]

Hi, I have a Dataflow in Power BI Service. 

I have added a new column to my dataflow which is 

DateTime.LocalNow() as a DateTime type. 

The aim is that whenever a refresh happens, this datetime changes.

However, when it refreshes, the time is 1 hour behind.. 

When i connect to my powerbi report, its also showing one hour behind

So i customised the column to :

DateTime.LocalNow() + #duration(0,1,0,0)

Now, this added 1 hour to the date time. However, when i connect my power bi to the dataflow. It still showing 1 hour behind

What is happening here?. The regional setting on my power bi desktop (Current file) is set to English (United Kingdom) 

Basically, i need the last refresh date time as a column and it needs to consider the time changes. 

Thanks 

[Anonymous]

@PPStar  So you're bringing in the MIN or MAX date from your dataflow into a card visualization? Are there unique dates in that column? What is your time in UTC? When you note that the column needs to consider time changes, are you referring to DST?

[PPStar]

I just want a date that shows the last refresh of the dataflow.  The last refresh datetime column is a DateTime format. 

It just going to be displayed as a Table in the report. 

[PPStar]

For me, UTC time is -1. 

I am on BST (British summer time), its 13:54 here now, so UTC is 12:54 

[Anonymous]

I thought that might be the case. It sounds like you might be dealing with a disconnect between your local time and the server time and might need to use a different M date function. Check out this blog and see if there's anything helpful to you: 

https://blog.crossjoin.co.uk/2017/03/28/daylight-saving-time-and-time-zones-in-m/

[PPStar]

This doesnt not work for me.. 

We have a global user base, i.e. users in US, Australia, UK, Europe etc. 

The solution needs to show to their local time. So for e.g. if the dataflow in the service shows UTC, depending on what country the user is in, the time should change to reflect their time zone. 

Is this even possible?

[Anonymous · Accepted solution]

@PPStar  You can use some simple API calls like what I have done in the attached .pbix. Remember though, the visualizations will still need to be refreshed aven after the dataflow and report refreshes.

[PPStar]

Thanks. Yes,  i thikn this is the only real way to do this. Bit of an overkill, but thank you very much.  can see why Dataflows work this way... Thanks for your time and help on this. 

[Anonymous]

You're welcome and I agree. I've seen how using date/time M functions can make it work, theoretically, but haven't been able to get it to work in practice.