Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DateDiff in CallDate Not Updating without a Data Refresh

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

 

Need Help
Tips and Tricks

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.