Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Community Champion
Community Champion

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!

Blog: nickyvv.com | @NickyvV


View solution in original post

2 REPLIES 2
nickyvv
Community Champion
Community Champion

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!

Blog: nickyvv.com | @NickyvV


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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors