DateDiff in CallDate Not Updating without a Data Refresh

[Anonymous]

I have created a dataset with Directqueries to data held on an on-premise SQL server. At the same time, I have created a CatDate Table with the formula:-

CalDate = CALENDAR(DATE(2020,1,1),DATE(2040,12,31))

There are a number of calculated columns in the table. One is the Date in LTC time. The formula is:-

DateLTC = [date] + 9/24

Then to produced a usable Dayoffset another column has the formula:-

DayOffset = DATEDIFF(TODAY(),CalDate[DateLTC],DAY)

Works Fine in Desktop, in Service it only works after 10 am, and only if you refresh the data. How do I get a 0 for today's date in Power BI Service, or is this the correct way and expected behaviours. 

TIA

[lbendlin]

What is LTC, and are you saying it is 9 hrs ahead of UTC?

[Anonymous]

Correct. The data is in UTC, but the users are in Sydney (UTC+9 currently).

The reason we are looking at this is the Date Slicer, relative date slider only works on UTC time, and therefore we cannot get today's information until after 9 am using that day. Getting a bit desperate to sort the issue out. Using a Date Offset and a slider to choose the dates we need was the last option to make sure that today was chosen, along with a number of previous days for comparison. 

The problem appears to be that we need to set a refreshing time for the CalDate Table, to get the datediff function to rerun, and choose the right offset for today. 

[lbendlin]

Aren't you on AEST?

Anyway, this might help:

https://community.powerbi.com/t5/Desktop/Convert-UTC-to-local-time-zone-in-DAX/td-p/43328

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

[Anonymous]

Thanks for the information, perhaps I am explaining the issue incorrectly. The issue is with the Column 

DayOffset = DATEDIFF(TODAY(),CalDate[DateLTC],DAY). In Desktop it refreshes regularly every 1 hr so it always returns the right number.

If I upload the PBIX on June 1 2020, then currently June 1, 2020, is DayOffset 0.

On June 2, 2020, DayOffset for June 2, 2020, should be  0, and June 1, 2020, should be -1

In desktop June 2  is DayOffset 0, in Service June 2  is still +1, and June 1 is still 0 until a manual refresh happens. 

Is that correct behaviour?

[lbendlin]

Remember that the Power BI service runs on UTC.

[Anonymous]

I understand that and don't believe it is the issue. I honestly believe the issue is that without a data refresh the CalDate Table does not appear to update the DateOffset Column. Is that expected behaviour, or should the column be dynamic and update every time the data is pulled?