cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adnankabina
Frequent Visitor

Refresh time on visual

I want to show Estern time on my visual. I have schedule for every 2 hours to refresh my data.  what I did on measure

Last Refreshed Time = UTCNOW()-TIME(4,0,0)

It is showing fine but it should stay same time until next refresh is done.  Below pic shows it refreshed at 6pm eastern time. If user goes to on 6:45pm and try to refreh from menue as show in pic should not change it.

 
 

adnankabina_0-1603588741781.png

 

 

 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @adnankabina ,

 

In your scenario, you should use Calculated Column or M Query instead of Measure.

The former will refresh time after the dataset was refreshed, the latter will refresh time after the page was refreshed.

For calculated column, right click on the table and create a new column using your formula.

For M Query, open query editor and add custom column with below formula.

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

1.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

7 REPLIES 7
RGavati1
Regular Visitor

I appreciate this entire thread of discussion, but it seems to be taking the System date (LocalNow) and adding it to the report.  My question is this:  I want the report to show the last date/time the report was refreshed, and “date/time” refers to the Refresh time set in the Gateway settings for refresh.  I know when that is supposed to be, because the report generating person, I set that date and time.  However, the consumer of the report does not necessarily know that and then it would be convenient to have a measure (or calculated column or…) that automatically shows when the actual last refresh date/time was so that the consumer is reassured that they are looking at “current” data.  I was looking at wrong a measure, but how do I access that date/time, set up in the Gateway settings, and integrate that with the report.??

v-jayw-msft
Community Support
Community Support

Hi @adnankabina ,

 

In your scenario, you should use Calculated Column or M Query instead of Measure.

The former will refresh time after the dataset was refreshed, the latter will refresh time after the page was refreshed.

For calculated column, right click on the table and create a new column using your formula.

For M Query, open query editor and add custom column with below formula.

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

1.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
AnkitKukreja
Solution Supplier
Solution Supplier

Hi @adnankabina 

 

I believe you're on the right track, but would you mind creating the same in Query Editor. 
Please use Blank query and write = DateTime.LocalNow(). 

 

Please refer to the below link for reference. it works fine for me and time only changes when I refresh the model or it's on schedule refresh.

https://www.youtube.com/watch?v=oN6mOmEruOQ

 

Hope it helps.

 

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

Thanks for help. After publishing it is showing me UTC time , how can i subtract 4 hours? any idea

Hi @adnankabina 

 

Yes, please use DateTime.From ( [DateTime] ) + #duration(0, +5,0,0)

Note : +5 is utc to my time zone (so change as per your need). And [DateTime] is my date time column already availbale.

 

 

Thanks,

Ankit

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

Hi AnkitKukreja,

 

Should I do in blank query or create a measure. I didnt get you.

 

This is what I did in blank query but go error.

= DateTime.From ( [DateTime] ) + #duration(0, +5,0,0)

 

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

Hi @adnankabina 

 

As you would have created your timestamp column by using blank query earlier, where you were getting time which was off by 4 hours. In the same table use this query by adding custom column.

 

Or in the same query use the below code, by deleting your full code in advance editor of this table and replace it by below code.

 

let
Source = DateTime.LocalNow(),
#"Converted to Table" = #table(1, {{Source}}),
#"Inserted Date" = Table.AddColumn(#"Converted to Table", "Date", each DateTime.Date([Column1]), type date),
#"Inserted Time" = Table.AddColumn(#"Inserted Date", "Time", each DateTime.Time([Column1]), type time),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Time",{{"Column1", "DateTime"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "DateTime MyZone", each DateTime.From ( [DateTime] ) + #duration(0, +5,0,0))
in
#"Added Custom"

 

Or you can just add custom column in your current table and use my last m code that I shared.

 

Thanks,

Ankit

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors