Publish gives different results then the Desktop version

[martijnth]

Dear Reader,

when i publish a dashboard with a DAX code like below:

Planned Num = CONCATENATE(round(FORMAT(now(),"hh")*55+right(format(now(),"hh:mm"),2)*(55/60),0)-55*6, " Planned Gt's")

the result it gives in Desktop is correct, however 'Published' it gives a totally different result, i have no idea why or how i can check where it is going wrong. All help is appreciated.

reg martijn

[tex628]

This is a wild guess, but sometimes it seems like the locale and timezones differ between desktop and service, meaning that your NOW() statement would return a different timestamp when you publish it!

 /Johannes

[martijnth]

Hi Tex628,

yes this would be my guess as well, but now i need a solution, any idea?

reg martijn

[tex628 · Accepted solution]

Appears that the NOW() Statement always returns UTC time and that can not be changed in service.... 

https://community.powerbi.com/t5/Service/Set-local-time-zone-Power-BI-Service/td-p/114120

I would suggest getting the local time in PowerQuery, just make a standalone 1 row table. Then use that instead of the now() statement!

DateTime.LocalNow()

/ Johannes

[martijnth]

unfortunately Format only works on falue, so i used 

Planned Num = CONCATENATE(round(FORMAT(UTCNOW()+2/24,"hh")*55+right(format(UTCNOW()+2/24,"hh:mm"),2)*(55/60),0)-55*6, " Planned Gt's")

works aswell, thanks all

[tex628]

You can check it by just putting a NOW statement in a card and comparing the results.. 

I'm gonna need some time to check if it's possible to change to locale in service. Otherwise you have to do some messed up solution with paralellperiod to account for the timedifference...

[vanessafvg]

@martijnth 

are you using the same data?

what is the published vs the deskop vs showing?

do you have filters on in the published?

[martijnth]

are you using the same data? YES

what is the published vs the deskop vs showing?

    Published: 112 Planned GT's, 

    Desktop: 221 Planned GT's

No filters, same report, just published