Last Refresh data issue

[mdaamirkhan]

Hi All

I have create Last Refresh Date using M Query and dax. i want to used GMT +6 time zone. Its working fine in PC in the pbix file but when I publish the report its showing wrong value. Can you help me what is the issue and provide me dax

Dax : 

LastRefreshedDiff =

/*Calculate the difference in days, hours and minutes from UTCNOW() and the lastRefreshedDate*/

VAR pNumOfMinutes = DATEDIFF(MAX('Data Refresh Time'[LastRefreshDate]), NOW() , MINUTE)

VAR pDIFF = pNumOfMinutes / (60.0000 * 24.0000)

VAR pDiffDays =

IF ( pDIFF >= 1, INT(pDIFF), BLANK () )

VAR pDiffHours =

INT ( ( pDIFF - pDiffDays ) * 24 )

VAR pDiffMinutes = pNumOfMinutes

- ( pDiffDays * 24

* 60 )

- ( pDiffHours * 60 )

/*Determine the Text Labels for days, hours and minutes*/

VAR DaysText =

SWITCH(TRUE(),

pDiffDays = 0, "",

pDiffDays = 1, FORMAT ( pDiffDays, "0" ) & " day",

FORMAT ( pDiffDays, "0" ) & " days"

)

VAR HoursText =

SWITCH(TRUE(),

pDiffHours = 0, "",

pDiffHours = 1, FORMAT ( pDiffHours, "0" ) & " hr",

FORMAT ( pDiffHours, "0" ) & " hrs"

)

VAR MinutesText =

SWITCH(TRUE(),

pDiffMinutes = 1, FORMAT ( pDiffMinutes, "0" ) & " min ago",

FORMAT ( pDiffMinutes, "0" ) & " mins ago"

)

RETURN

IF (

NOT ( ISBLANK ( MAX('Data Refresh Time'[LastRefreshDate]) ) ),

COMBINEVALUES ( " ", DaysText, HoursText, MinutesText )

)

M Query: 
#"Added Custom" = Table.AddColumn(#"Changed Type", "LastRefreshDate", each DateTimeZone.ToLocal([DateTime])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"LastRefreshDate", type datetimezone}})
in
#"Changed Type1"

Before published:
Before published

After published (Which showing wrong value)
 

[v-robertq-msft · Accepted solution]

Hi, @mdaamirkhan 

According to your description and sample DAX formula, it seems that you are facing the problem that the time zone can’t sync when the report is published to the web service.

This is because the power bi service only supports UTC DateTime currently. It doesn’t contain any features that can auto transform DateTime based on location.

Here’s a blog that describes several ways to deal with this kind of problem:

https://radacad.com/solving-dax-time-zone-issue-in-power-bi

You can also vote for and comment on the below idea submitted by other users, so the Power BI team can update that issue asap.

https://ideas.powerbi.com/ideas/idea/?ideaid=6636c895-418e-4477-9e68-f216c3162015

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-robertq-msft]

Hi, @mdaamirkhan 

What’s the difference between the DAX formula you posted here and the DAX formula you posted above?

According to the sample picture you posted before, I think the reason for this problem is that the UTC DateTime in the Power BI service is 7 hours earlier than the UTC DateTime in the Power BI desktop. If you want to make the measure display correctly in the Power BI service, I think the easiest way is to change the time of NOW() in the DAX formula, you can change the DAX formula like this:

LastRefreshedDiff =

/*Calculate the difference in days, hours and minutes from UTCNOW() and the lastRefreshedDate*/

VAR pNumOfMinutes = DATEDIFF(MAX('Data Refresh Time'[LastRefreshDate]), NOW() + (7/24), MINUTE)

VAR pDIFF = pNumOfMinutes / (60.0000 * 24.0000)

VAR pDiffDays =

IF ( pDIFF >= 1, INT(pDIFF), BLANK () )

VAR pDiffHours =

INT ( ( pDIFF - pDiffDays ) * 24 )

VAR pDiffMinutes = ABS(pNumOfMinutes

- ( pDiffDays * 24

* 60 )

- ( pDiffHours * 60 ) )

/*Determine the Text Labels for days, hours and minutes*/

VAR DaysText =

SWITCH(TRUE(),

pDiffDays = 0, "",

pDiffDays = 1, FORMAT ( pDiffDays, "0" ) & " day",

FORMAT ( pDiffDays, "0" ) & " days"

)

VAR HoursText =

SWITCH(TRUE(),

pDiffHours = 0, "",

pDiffHours = 1, FORMAT ( pDiffHours, "0" ) & " hr",

FORMAT ( pDiffHours, "0" ) & " hrs"

)

VAR MinutesText =

SWITCH(TRUE(),

pDiffMinutes = 1, FORMAT ( pDiffMinutes, "0" ) & " min ago",

FORMAT ( pDiffMinutes, "0" ) & " mins ago"

)

RETURN

IF (

NOT ( ISBLANK ( MAX('Data Refresh Time'[LastRefreshDate]) ) ),

COMBINEVALUES ( " ", DaysText, HoursText, MinutesText )

)

If you still have a problem, you can your sample pbix file(without sensitive data) so that we can help you in advance.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mdaamirkhan]

Hi 

What is the issue 

in pbix file I am getting below value 

But in Power BI service I am getting different value

[v-robertq-msft · Accepted solution]

Hi, @mdaamirkhan 

The reason why this problem happens is explained in my first reply:

This is because the power bi service only supports UTC DateTime currently. It doesn’t contain any features that can auto transform DateTime based on location.

And it’s also an issue with great influence in the Power BI idea, you can also vote for and comment on the below idea submitted by other users, so the Power BI team can update that issue asap.

https://ideas.powerbi.com/ideas/idea/?ideaid=6636c895-418e-4477-9e68-f216c3162015

In my opinion, if you usually view your reports in the Power BI service most of the time, I suggest you to use the DAX formula I posted above to make the DateTime display correctly in the Power BI service. This is also the most effective way to use at present.

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-robertq-msft · Accepted solution]

Hi, @mdaamirkhan 

According to your description and sample DAX formula, it seems that you are facing the problem that the time zone can’t sync when the report is published to the web service.

This is because the power bi service only supports UTC DateTime currently. It doesn’t contain any features that can auto transform DateTime based on location.

Here’s a blog that describes several ways to deal with this kind of problem:

https://radacad.com/solving-dax-time-zone-issue-in-power-bi

You can also vote for and comment on the below idea submitted by other users, so the Power BI team can update that issue asap.

https://ideas.powerbi.com/ideas/idea/?ideaid=6636c895-418e-4477-9e68-f216c3162015

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mdaamirkhan]

I am getting neative hours in web . How to resloved that

 My query below

LastRefreshedDiff =

/*Calculate the difference in days, hours and minutes from UTCNOW() and the lastRefreshedDate*/

VAR pNumOfMinutes = DATEDIFF(MAX('Data Refresh Time'[LastRefreshDate]), NOW(), MINUTE)

VAR pDIFF = pNumOfMinutes / (60.0000 * 24.0000)

VAR pDiffDays =

IF ( pDIFF >= 1, INT(pDIFF), BLANK () )

VAR pDiffHours =

INT ( ( pDIFF - pDiffDays ) * 24 )

VAR pDiffMinutes = ABS(pNumOfMinutes

- ( pDiffDays * 24

* 60 )

- ( pDiffHours * 60 ) )

/*Determine the Text Labels for days, hours and minutes*/

VAR DaysText =

SWITCH(TRUE(),

pDiffDays = 0, "",

pDiffDays = 1, FORMAT ( pDiffDays, "0" ) & " day",

FORMAT ( pDiffDays, "0" ) & " days"

)

VAR HoursText =

SWITCH(TRUE(),

pDiffHours = 0, "",

pDiffHours = 1, FORMAT ( pDiffHours, "0" ) & " hr",

FORMAT ( pDiffHours, "0" ) & " hrs"

)

VAR MinutesText =

SWITCH(TRUE(),

pDiffMinutes = 1, FORMAT ( pDiffMinutes, "0" ) & " min ago",

FORMAT ( pDiffMinutes, "0" ) & " mins ago"

)

RETURN

IF (

NOT ( ISBLANK ( MAX('Data Refresh Time'[LastRefreshDate]) ) ),

COMBINEVALUES ( " ", DaysText, HoursText, MinutesText )

)