Power BI Online Service today() not refreshing

[liujessie39]

I've created two columns using DAX formulas on Power BI Desktop, one is to calculate the number of days until a date listed in a separate column (using =[DATE]-today()) and the other is to return 1 or 0 depending on if there's 21 days until the listed date (using an IF function). The purpose is to be able to send an email alert when it's 3 weeks from the date listed. I think that I can do that with a card set to the max of the column returning 1 or 0 and send a Power BI alert for that card and use Microsoft Flow to send an email then. However, I can't get the column's formulas to update on ther Power BI Service. It updates fine each day (the "days until" steadily decreasing) in Power BI Desktop, but the number is frozen on the day I uploaded the report and dataset online. Even when I manually publish the report/dataset again from Power BI Desktop, the numbers don't update. How do I get the today() function, or any of the functions in the columns, to update on their own? Or is there a way I can schedule desktop to publish the report (if it will update the numbers) every day? If anyone has any better suggestions for how I can schedule an email alert to certain people 3 weeks from a listed date, that would also be very helpful!

[v-jiascu-msft]

Hi @liujessie39,

I will check my test mode tomorrow. BTW, did you set the scheduled refresh?

Best Regards,

Dale

[liujessie39]

@v-jiascu-msft I did not, but I did look into it. Since it's only a table instead of an outside data source I wasn't sure if a scheduled refresh would be necessary or in what way to set it up. I considered if republishing from desktop would work but when I manually did so the numbers did not update online even though they were updated in desktop. 

[pdacheva]

Hi liujessie39, did you manage to solve this issue? I have the same problem. I calculate a state based on TODAY() date, but my published report does not update. I have to reload it from the desktop everytime. 

The manual Refresh did not work either.

Thanks in advance!

[LangleyB]

Greetings,

I seem to have a similar issue. I have created a DAX date table with several calculated columns utilizing the function TODAY(). Yesterday I published my report to the PBI service. Today, none of these columns have updated on the PBI service, however, all updated on my desktop version upon opening the file.

That said, one standalone measure using TODAY() did refresh on the PBI service. This leads me to believe that TODAY() behaves differently in measures compared to in columns on the PBI service.

Today I have marked the table as a "Date Table"... I'm hoping that will fix the problem. Any ideas would be appreciated.

Here is my DAX date table:

DDateTable = 
ADDCOLUMNS (
CALENDAR (DATE(2010,1,1), DATE(2030,12,31)),
"ExcelDate", FORMAT([Date], "####" ),
"Year", YEAR([Date]),
"Quarter", SWITCH(MONTH([Date]),1,1,2,1,3,1,4,2,5,2,6,2,7,3,8,3,9,3,10,4,11,4,12,4),
"MonthNum",MONTH([Date]),
"MonthMMM",FORMAT([Date],"mmm"),
"MonthMMMM",FORMAT([Date],"mmmm"),
"DayOfMonth",DAY([Date]),
"DayOfYear",DATEDIFF ( DATE ( YEAR ( [Date] ), 1, 1 ), [Date], DAY ) + 1,
"WeekdayNum",WEEKDAY([Date],2),
"WeekdayDDD",FORMAT([Date],"ddd"),
"WeekdayDDDD",FORMAT([Date],"dddd"),
"WeekendWeekday",IF(WEEKDAY([Date],2)>5,"Weekend","Week Day"),
"FYShort",YEAR([Date])+(MONTH([Date])>=4),
"FYLong",YEAR([Date])+(MONTH([Date])>=4)-1&"-"&YEAR([Date])+(MONTH([Date])>=4),
"FYQuarter",SWITCH(MONTH([Date]),1,4,2,4,3,4,4,1,5,1,6,1,7,2,8,2,9,2,10,3,11,3,12,3),
"FYPeriod",SWITCH(MONTH([Date]),1,10,2,11,3,12,4,1,5,2,6,3,7,4,8,5,9,6,10,7,11,8,12,9),
"FYNum",VALUE(RIGHT(YEAR([Date])+(MONTH([Date])>=4),2)),
"FYContextNum", (YEAR([Date])+(MONTH([Date])>=4))-(YEAR(TODAY())+(MONTH(TODAY())>=4)),
"FYContextVerbose", IF(YEAR([Date])+(MONTH([Date])>=4)-(YEAR(TODAY())+(MONTH(TODAY())>=4))=0,"Current FY",IF(YEAR([Date])+(MONTH([Date])>=4)-(YEAR(TODAY())+(MONTH(TODAY())>=4))=1,"Next FY",IF(YEAR([Date])+(MONTH([Date])>=4)-(YEAR(TODAY())+(MONTH(TODAY())>=4))=-1,"Previous FY",IF(YEAR([Date])+(MONTH([Date])>=4)-(YEAR(TODAY())+(MONTH(TODAY())>=4))>1,(YEAR([Date])+(MONTH([Date])>=4))-(YEAR(TODAY())+(MONTH(TODAY())>=4))&" FYs From Now",((YEAR([Date])+(MONTH([Date])>=4))-(YEAR(TODAY())+(MONTH(TODAY())>=4)))*-1&" FYs Ago")))),
"FYQContextNum", DATEDIFF(TODAY(),[Date],QUARTER),
"FYQContextVerbose", IF(DATEDIFF(TODAY(),[Date],QUARTER)=0,"Current Quarter", IF(DATEDIFF(TODAY(),[Date],QUARTER)=-1,"Previous Quarter", IF(DATEDIFF(TODAY(),[Date],QUARTER)=1,"Next Quarter",IF(DATEDIFF(TODAY(),[Date],QUARTER)>1,DATEDIFF(TODAY(),[Date],QUARTER)&" Quarters From Now",(DATEDIFF(TODAY(),[Date],QUARTER))*-1&" Quarters Ago")))),
"MonthContextNum",DATEDIFF(TODAY(),[Date],MONTH),
"MonthContextVerbose",IF(DATEDIFF(TODAY(),[Date],MONTH)=0,"Current Month",IF(DATEDIFF(TODAY(),[Date],MONTH)=-1,"Previous Month",IF(DATEDIFF(TODAY(),[Date],MONTH)=1,"Next Month",IF(DATEDIFF(TODAY(),[Date],MONTH)>1,DATEDIFF(TODAY(),[Date],MONTH)&" Months From Now",DATEDIFF(TODAY(),[Date],MONTH)*-1&" Months Ago")))),
"DayContextNum",DATEDIFF(TODAY(),[Date],DAY),
"DayContextVerbose",IF(DATEDIFF(TODAY(),[Date],DAY)=0,"Today",IF(DATEDIFF(TODAY(),[Date],DAY)=1,"Tomorrow",IF(DATEDIFF(TODAY(),[Date],DAY)=-1,"Yesterday",IF(DATEDIFF(TODAY(),[Date],DAY)>1,DATEDIFF(TODAY(),[Date],DAY)&" Days From Now",DATEDIFF(TODAY(),[Date],DAY)*-1&" Days Ago"))))
)

Cheers,

-Ben

[Anonymous]

Hello @pdacheva @v-jiascu-msft @Vicky_Song 

has anyone solved this problem?

I met this problem at the start of this week, before everything was no problem.

Please tell me the solutions if you have found it 🙂

Thank you and best Regards

Xiaoning

[pdacheva]

Hi @Anonymous,

I haven't found a solution. I did re-installed the Desktop app from the Microsoft Store, but didn't really changed the performance. For me the biggest issue is the performance  - it takes me from 5 to 10 minutes to save a calculated column or a measure. And I believe it's due to the DAX tables I have in my report. Calculated DAX tables seem to be no go for this version.

Yesterday I found few more bugs like - Power BI doesn't respect the Format type change I put in the query transformations, therefore I need to change formats again in the Table itself. Loosing Columns/Measures created in a table after pointing to a different file/folder in the query, etc.

[Anonymous]

Hello @pdacheva 

I think I have just found the solution to my case. I am in Germany and I set the planned refresh time at 1:00am and this is 11:00pm (one day early) in GTM time. And I have also noticed what Power BI explains about today() function: If the TODAY function does not update the date when you expect it to, you might need to change the settings that control when the column or workbook is refreshed.

So it means Power BI online calculates today according to the real refresh time. Now I have changed it to 4:00am and it works again now. 🙂

Plus: according to performance issue, I used before my laptop and now changed to a PC with  a better processer. Then Dax works much faster. 

Or you may also delete the columns from the tables which you don't need, it can also improve the refresh and loading performances.

Thank you and best Regards

Xiaoning

[Anonymous]

I have had a similar issue -

Have a caclulated column with Today() in it and value remains the same as it was once published. Scheduled or Manual refresh also doesnt change the values

Have checked the forum and not seen anyone with a firm resolution.

Can someone help 

What is the process to raise a ticket if it indeed seems to be a PowerBI Issue 

[Anonymous]

Hello @Anonymous ,

for me it has worked when I changed the auto freshing time every day. Today() funtion on the web calculates accroding to the refresh time (I suppose the time zone is American time). Because I am in Germany, when I refresh the data every day at 1:00 am, today() function on the web doesn't work. But after I changed it to 4:00am, it works again.

I hope I can help you.

Best Regards,

Xiaoning