cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Agaidar
Helper I
Helper I

Whye my dates changes when I publish my report to Power Bi Web

Hi Team,

 

I'm based in New Zealand and using power bi to track our daily sales. We don't have sales everyday and I have created a calendar table ( = calendar (startdate, endate)) and I'm using below expression to calculate MTD  working days:

 

Working Days MTD =
CALCULATE (
    COUNTROWS ( 'Calendar'  ),
    FILTER (
        'Calendar',
        'Calendar'[Local Date].[Day] < DAY ( TODAY () )
            && 'Calendar'[Local Date].[MonthNo] = MONTH ( TODAY () ) && 'Calendar'[Local Date].[Year] = YEAR( TODAY())
            && 'Calendar'[Weekdays] <= 5
    )
)
 
It's working, however when I publish my report to Power Bi Web Serivce it goes -13h back and my "today" day counts as yesterday, till 1pm NZ time.
 
So far I tried:
1. Change languages/region and languages in browser
2. Add new column:
Local Date = FORMAT('Calendar'[Date]+0.7, "General Date")
 
Thankful in advance for your help, I still can add +1 day to today () and remove it after 1pm but I dont think this is right way to work with power bi
 
Regards,
Aidar
1 ACCEPTED SOLUTION
nickyvv
Super User
Super User

Hi Aidar,

 

Because Power BI is using GMT (or UTC, not sure) you get the difference in time zones for TODAY or NOW functions.

You can solve it with DAX in your data model or with M in Power Query.

Reza Rad did a good job explaining it here:

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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


Blog: nickyvv.com | @NickyvV


View solution in original post

2 REPLIES 2
nickyvv
Super User
Super User

Hi Aidar,

 

Because Power BI is using GMT (or UTC, not sure) you get the difference in time zones for TODAY or NOW functions.

You can solve it with DAX in your data model or with M in Power Query.

Reza Rad did a good job explaining it here:

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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


Blog: nickyvv.com | @NickyvV


View solution in original post

Thanks!

 

My final solution based on Reza's post here:

 

 
CALCULATE (
    COUNTROWS ( 'Calendar'  ),
    FILTER (
        'Calendar',
        'Calendar'[Date].[Day] < DAY ( NOW()+(12/24) )
            && 'Calendar'[Date].[MonthNo] = MONTH ( TODAY () ) && 'Calendar'[Date].[Year] = YEAR( TODAY())
            && 'Calendar'[Weekdays] <= 5
    )
)

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!