Latest Week not updating in Power BI Service

[_Amy_]

I have a weekly report which is emailed to a group of people each Monday morning. I always want the users to see the most recent week ending data but have the option to select previous weeks.

I have set up a new column in my Power Query Editor date table which indicates which Week End is the ‘Latest Week’

I have the report set up to refresh daily in Power BI service, however the dates in the slicer in here don’t update properly. For example, the refresh on 25/4/2022 should have changed the week ending 23/4/2022 to the “Latest Week” and the week ending 16/4/2022 should be added to the  list.

It works in the Power BI Desktop Refresh, but for some reason it doesn’t work in the Power BI Service.

Power BI Service:

Power BI Desktop Refresh:

I have a Date Table in Power Query Editor where I have added the following columns:

The formula for Week End is:

The “Week End” column is added as a slicer in the report.

Hoping someone can help me 🙂

[_Amy_ · Accepted solution]

Microsoft Support helped me resolve this time zone issue. 

I was using a filter on my slicer "Is Before Today?"  - the problem was that the Power BI Service uses UTC time to work out what "today" is,  whereas Power BI Desktop uses local time which is why it was working on Desktop but not on Service.  Where I am in Australia is 10 hours ahead of UTC so on Monday morning here, it is still Sunday in UTC. 

I used the following links to resolve the issue, which involves converting dates to date/time/timezone in Power BI Desktop.

https://www.thepoweruser.com/2019/10/21/handling-different-time-zones-in-power-bi-power-query/

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

[Anonymous]

Hi  @_Amy_ ,

You seem to have no current value to judge, you can consider using dax to complete.

I created some data:

Create calculated column.

date1 =
IF(
    WEEKNUM('Table'[Date],1)=WEEKNUM(TODAY(),1)-1,"Latest Week",FORMAT('Table'[Date],"ddddd"))

Use [date1] as the slicer:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[_Amy_]

Thankyou for this, that calculated column is a much more elegant way to calculate the current week than I was doing. However, this didn't resolve the issue as it ended up being a time zone issue. 

The time zone on Power BI Desktop is local time, but when published to the Power BI Service, it uses UTC time. As I am in Australia, where local time is 10 hours ahead of UTC, "TODAY" in Australia is often yesterday in other parts of the world.