Why Power BI refresh date not align with refresh date of the dataset in workspace

[aiyosap]

Hi all,

I am trying numerous ways to solve this and not able to.

We have a Power BI dashboard with a refresh date not similar to the refresh date of the dataset shown in the workspace even though we have scheduled the refresh background. Our timezone is Sydney Australia

Example:

1. Power BI dashboard refresh date showing date last refresh as "5/17/2022 10:26:29PM"

2. The refreshed date in the workspace showing "18/05/22, 08:26:32" for the report and datasets.

3) Our setting in the scheduled refresh:

4) In the DAX development screen, we created a measurement object that tied to the "Data Refresh" model."Date Refresh" model in Query screen

DAX object built on the "Date Refresh" model

Date Last Refreshed1 = VALUES('Date Refresh'[Date Last Refreshed])

You can see that the data refreshed date between Dashboard in no. 1 and auto refreshed date in no. 2 are different.

Hope you can shed some light. Thank You.

Aiyo

[mahenkj2]

and use that new column in your dashbaord

[mahenkj2]

It seems refresh time in your dashboard is UTC time. it is 10 hours difference. You can add 10 hours in refresh datetime of dashboard to align.

[aiyosap]

Hi Mahenkj2,

How do you add 10 hours in my current below?

Date Last Refreshed1 = VALUES('Date Refresh'[Date Last Refreshed])

Becasue if you meant below, it is incorrect.

Date Last Refreshed1 = VALUES('Date Refresh'[Date Last Refreshed]) + 10

Regards,

Aiyo

[danextian]

Hi @aiyosap ,

In DAX you can do this as a measure.

Date Last Refreshed1 =
MAX ( 'Date Refresh'[Date Last Refreshed] ) + DIVIDE ( 10, 24 )
//24 hours in a day

[aiyosap]

Hi Tian,

Totally out. You notice the big difference. 

[mahenkj2]

Instead you can add an addition column in query screen:

[Date Last Refreshed] + #duration(0,2,0,0)

[mahenkj2 · Accepted solution]

Hi @aiyosap ,

I made a mistake here, used 2 instead of 10 needed in your case. Anyway, see step wise instruction below:

1. Your original column 'Date Last Refreshed1' (I added 1 to it)

= #table(type table[Date Last Refreshed1=datetime],{{DateTime.LocalNow()}})

2. add a custom column to make 10 hour difference, keep column name as 'Date Last Refreshed':

3. Convert this new column to date time:

4. Remvove the original column, as it is not needed now:

You are done! Then you should not need any further changes in your DAX formulae as such.

I think alternatively, you could have also add timezone figures with this M function:

https://docs.microsoft.com/en-us/powerquery-m/datetime-addzone

[aiyosap]

Hi Mah,

May I ask if I need to manually adjust the time hour in the m query you provide below when come to day light saving?

#"Added Custom" = Table.AddColumn(Source, "Date very last refreshed", each [Date Last Refreshed] + #duration(0,10,0,0)),

Do I adjust the day light saving in the #duration (0, 11,0,0) if increase 1 hour during day light saving and  #duration(0,9,0,0)) if decrease 1 hour during day light saving?

Your solution is correct but I will test it out and let you know.

Regards,

Aiyo

[aiyosap]

Hi Mah,

I typed in the query and it hits error.

= Table.AddColumn(Source, "Custom", each Table.AddColumn("Custom", each[Date Last Refreshed]+ #duration(0,2,0,0))

Error message - 

Expression.SyntaxError: Token RightParen expected.

0001: Table.AddColumn(Source, "Custom", each Table.AddColumn("Custom", each[Date Last Refreshed]+ #duration(0,2,0,0)) ---->