Last refresh datetime format to only display full hours

[Anonymous]

So I got this calculated column " Source = DateTimeZone.UtcNow()+#duration(0, 2, 0, 0)" Now if it refreshes it displays it as for example17:14:49. I set the formatting in powerbi itself. BUT I want if the time it refreshes is 17:14:49 it to only display 17:00:00. so only full hours. My refresh time is set at every hour starting at 9:00:00 so displayed should be 9:00:00 10:00:00 etc. I don't know how to add this filtering or fotmatting to my formula.

Btw the reason i add 2 hours is cause in powerbi service it uses UTC as refresh time so im cancelling timezone difference.

Thx in advance.

[Mariusz]

Hi @Anonymous 

Sorry, this will include the duration.

= #time( Time.Hour( DateTimeZone.UtcNow() + #duration(0, 2, 0, 0) ), 0, 0 )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

hi @Mariusz 

This solution gives me " 31/12/1899 09:00:00" so somewhere the date doesn't get passed. To make clear i want the date AND the time in full hours not only the correct time. Sorry if this wasn't made clear before.

[Mariusz · Accepted solution]

Hi @Anonymous 

Sure try this.

let
    DateTimeNow = DateTimeZone.UtcNow() + #duration(0, 2, 0, 0),
    TimePlus2 = #time( Time.Hour( DateTimeNow ), 0, 0 ),
    DateTimePlus2 = DateTime.Date( DateTimeNow ) & TimePlus2 
in
    DateTimePlus2

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Mariusz]

Hi @Anonymous 

Try this.

= #time( Time.Hour( DateTimeZone.UtcNow() ), 0, 0 )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

Hi @Mariusz 

In this solution is the 2 hours still added? I don't see the duration added.