Calculated field using DAY(TODAY()) not updating in PowerBI Service

[IanDixon]

HI,

Part of my data is produced on the 15th of the month, basically on the 15th anything not entered is automatically updated by the appilication as "Missing".

I have a gateway from the Power BI service to the application database which refreshes the data on a daily basis or when refresh is clicked.

As the missing data isnt updated to the 15th of the month in the database, using the standard Month to work out aging wasn't working (or at least i couldn't figure it out) so I have a calculated field which uses an IF statement and DAY(Today()) < 15  to work out the aging of missing data.

Aging = IF(DAY(TODAY()) < 15, SWITCH (
TRUE (),
vwWOTimeLiveLeftHours[AGEMONTHS] >= -1, "0 Current",
vwWOTimeLiveLeftHours[AGEMONTHS] <= -2
&& vwWOTimeLiveLeftHours[AGEMONTHS] >= -3, "1 - 3 Months",
vwWOTimeLiveLeftHours[AGEMONTHS] <= -4
&& vwWOTimeLiveLeftHours[AGEMONTHS] >= -6, "4 - 6 Months",
vwWOTimeLiveLeftHours[AGEMONTHS] <= -7, "7 + Months", ""
), SWITCH (
TRUE (),
vwWOTimeLiveLeftHours[AGEMONTHS] >= 0, "0 Current",
vwWOTimeLiveLeftHours[AGEMONTHS] <= -1
&& vwWOTimeLiveLeftHours[AGEMONTHS] >= -3, "1 - 3 Months",
vwWOTimeLiveLeftHours[AGEMONTHS] <= -4
&& vwWOTimeLiveLeftHours[AGEMONTHS] >= -6, "4 - 6 Months",
vwWOTimeLiveLeftHours[AGEMONTHS] <= -7, "7 + Months", ""
))

AGEMONTHS is just the standard DATEDIFF

AGEMONTHS = DATEDIFF(vwWOTimeLiveLeftHours[DATENOW],vwWOTimeLiveLeftHours[PeriodBooked],MONTH)

When using Power BI Desktop this field updates automatically whenever the data is refreshed and the aging is correctly shown for the data on the report and dashboards. AGEMONTHS is show correctly and the Aging is Correct based on the caculated field,

In the Published report on the Power BI Service, AGEMONTHS is updated correctly based on the current date, 

The problem is that DAY(Today()) is the Day last time the report was refreshed on the Desktop PowerBI and published to the Power BI Service and not the current Day. For some reason DAY(Today()) is not reflecting the current Day of the Month on the Power BI Service.

New data is assigned into Aging buckets  though based on the last time the report was published to the service not the current Day, so the IF/Switch Statement is working, but just with the incorrect Day.

Any one encountered this problem ?

[Greg_Deckler]

Is Aging a Column or a Measure? I assume Column.

[IanDixon]

Yes Aging is a column

[v-shex-msft]

HI @IanDixon,

I'd like to suggest use UTCTODAY and UTCNOW to calculate datetime with UTC format. (AFAIK, current service will analyze datetime as UTC format)

Power BI Desktop February Feature Summary

Regards,

Xiaoxin Sheng

[GilbertQ]

Hi there, is the report being refreshed daily?

The reason that I ask is that the dashboard would need to be refreshed daily for it to calculate the new TODAY()