cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Agaidar Frequent Visitor
Frequent Visitor

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

Accepted Solutions
nickyvv Member
Member

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

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

View solution in original post

2 REPLIES 2
nickyvv Member
Member

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

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

View solution in original post

Agaidar Frequent Visitor
Frequent Visitor

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

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
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)