result based on TODAY() is not being updated

[bonny666]

In my source excel there's the following formula: 

=IF(Till="","",
IF(DATEDIF(Till,TodayDate,"y"), DATEDIF(Till,TodayDate,"y")&"y ","")&
IF(DATEDIF(Till,TodayDate,"ym"), DATEDIF(Till,TodayDate,"ym")&"m ","")&
IF(DATEDIF(Till,TodayDate,"md"), DATEDIF(Till,TodayDate,"md") &"d ",""))

TodayDate = Today()

The result of this formula is in my POWER BI report. It just a formula that shows how many days/months/years have passed between something in the cell next to it and today.

The problem is that when I run the report in PowerBI, it will not update the TODAY() it seems, so the result is showing wrong. if I will open the excel, it will recalculate it then and then I save  the excel and then refresh the report in PowerBI then it will be ok, but of course I don't want to do it every time I need to use this report..

[Anonymous]

From reading your description, you appear to have used Today() as part of a calculated column.  Calculated columns are only updated at the time of refresh, not during the usage of a report.

If you want a dynamically changing result, you would need to do this inside a Measure, or set your dataset to refresh once a day.

[bonny666]

No, i don't use a calculated column.. how can I use a calculated column in PowerBI service?

I couldn't find the option..

[bonny666]

Any reply on this?

[TeigeGao]

Hi @bonny666 ,

According to your description, my understanding is that you are publishing the report to PowerBI Service, but the TODAY() function has not been refreshed on PowerBI Service, I would suggest you set the TODAY() data in the dataset, then it will be refreshed.

Best Regards,

Teige

[bonny666]

I couldn't find the option.. I don't see any way to do any calculation in Power BI service

[bonny666]

Yes, you identified the problem correctly 🙂

Can you please explain how to do it in the dataset? I don't see how to do it in PowerBI Service