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

[Anonymous]

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

[nickyvv · Accepted solution]

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

[Anonymous]

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

    )

)