cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
aimsc
Regular Visitor

Date calculating differently on desktop and web due to formatting

I have a measure which calculates the number of working days within the current month (up to yesterday) with the following calculation: 

 

Working Days = CALCULATE(SUM(Holidays[Working Day]), FILTER(Holidays, Holidays[Date] >= ([First date in slicer])),
FILTER(Holidays, Holidays[Date] <= [Latest Date in slicer]))

 

The Holiday's DB is stored online and has 3 columns, Day (this is a text field for an explanation of why a week day isn't a working day e.g. Christmas day etc.), Date (formatted in UK format) and Working Day (either 1 or 0) whereby 1 is a working day and 0 isn't. 

 

The first date in slicer is the first of the current month: DATE(YEAR(TODAY()), MONTH(TODAY() -1), 1)

The latest date in slicer is yesterday (as we run the report for the last full day which will always be the day before it's ran: TODAY() - 1

 

When I run it in Power BI desktop, I get working days as 4. When I upload it and run it on Power BI Online, I get working days as 5. This then impacts a whole table as the table is divded by the number of working days. 

 

Can anyone help? I have changed the Power BI online language settings to be default already and that has updated the date formats on the slicers from US to UK but it is still saying 5 working days and not 4. 

 

Thanks in advance! 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Date calculating differently on desktop and web due to formatting

HI @aimsc,


First, if you direct use number to calculate with date, calculate time unit is days.

Second, utctoday not contains time part, so I think you can use utcnow.

Finally, BST timezone is utc +1, I think you should use + 1 hour instead -1.

 

First date in slicer =
VAR bst =
    UTCNOW () + TIME ( 1, 0, 0 )
RETURN
    DATE ( YEAR ( bst ), MONTH ( bst ), 1 )

Latest Date in slicer =
UTCTODAY () + TIME ( 1, 0, 0 )

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

8 REPLIES 8
Super User IV
Super User IV

Re: Date calculating differently on desktop and web due to formatting

Is this in a dashboard tile or in a report that you see the 5?


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

aimsc
Regular Visitor

Re: Date calculating differently on desktop and web due to formatting

Hi @Greg_Deckler I put it in a card on a report to view the number in both Desktop and Online.

 

I know it's wrong because when I manually calculate the average, it is using 5 and not 4 e.g. it is doing 964 / 5 to get 193 rather than 964 / 4 to get 241. 

Community Support
Community Support

Re: Date calculating differently on desktop and web due to formatting

HI @aimsc,

 

AFAIK, current power bi service will analysis datetime as UTC format.

For UTC datetime, it equal to zero timezone datetime, it is different as local datetime. I think your issue is related to it.

 

 

Please refer to following link which told about this issue:

Time in PBI Service is inconsistent with the local time (non-UTC time) displayed in PBI Desktop

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
aimsc
Regular Visitor

Re: Date calculating differently on desktop and web due to formatting

Thanks for your reply @v-shex-msft

 

I made the change as suggested, using +1 as I'm in BST, however, that now makes the desktop version incorrect too and today they are dividing by 6 working days rather than 5. 

Community Support
Community Support

Re: Date calculating differently on desktop and web due to formatting

HI @aimsc,

 

I'd like to suggest you use UTCNOW and UTCTODAY function to use UTC format to deal with you records.

 

For detail information about these function, please refer to below link:

Power BI Desktop February Feature Summary

Spoiler

UTCNOW() and UTCTODAY()

We've added two new DAX functions this month that help if you're working with date-time data across timezones. DAX has long supported the NOW() and TODAY() functions that return time and date in the timezone that the function's being used - so if a .pbix file is passed to someone in a different timezone they'll see different results. UTCNOW() and UTCTODAY() will always return the current time or date in UTC so you can guarantee consistent results wherever you are (and also when you upload the workbook to the Power BI service).

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
aimsc
Regular Visitor

Re: Date calculating differently on desktop and web due to formatting

Hi @v-shex-msft

 

I updated the following and it still has 6 working days: 

 

First date in slicer = DATE(YEAR(UTCTODAY()), MONTH(UTCTODAY() -1), 1)

Latest Date in slicer = UTCTODAY() - 1

 

Many thanks, 

Aimee  

Community Support
Community Support

Re: Date calculating differently on desktop and web due to formatting

HI @aimsc,


First, if you direct use number to calculate with date, calculate time unit is days.

Second, utctoday not contains time part, so I think you can use utcnow.

Finally, BST timezone is utc +1, I think you should use + 1 hour instead -1.

 

First date in slicer =
VAR bst =
    UTCNOW () + TIME ( 1, 0, 0 )
RETURN
    DATE ( YEAR ( bst ), MONTH ( bst ), 1 )

Latest Date in slicer =
UTCTODAY () + TIME ( 1, 0, 0 )

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

aimsc
Regular Visitor

Re: Date calculating differently on desktop and web due to formatting

Thanks @v-shex-msft that worked but with the -1 hour. 

 

Thanks so much!! 

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors