Forum Discussion
365 data inconsistent with the desktop
Solved the problem, still not quite sure what was tripping up;
In the Power BI query editor it was clear that the date columns were being treated as text, I simply changed type for the 4 affected columns to Date.
I also tweaked the formulas mentioned to remove MONTH and YEAR to use .[MonthNo] and .[Year] instead.
Republished and bingo, problem gone.
So best guess is that there's some implicit type conversion server side that defaults to US language when handling dates.
Thanks for the clues,
cheers,
Dave
Interesting idea guavaq, certainly good to know.
Yes to dates and we're in GMT, however the difference in the data isn't explained by time lag. Values are 61 instead of 9, the diffence would take many months to accumulate.
The other key is that I can publish and the value will be 9 briefly before reverting back to 61. I just use these as an example, the values are wrong across the whole report.
It reminds me of a feature in windows where DLLs would be protected so it you updated and overwrite Windows would very quietly revert them back without telling you. In the meantime you're stuck with an app that's complaining.
cheers,
Dave
Hi Stormkahn
Ok so it is not based on the dates.
What happens now is if you upload a Power BI report that is connected to a Gateway, upon upload it will refresh the data via the Gateway on upload. This is to ensure that it has got the latest version of the data.
You can verify this if you go into the Refresh History and you will see the time that you uploaded it, it did indeed refresh the data.
I have not come across datasets being different because of the Gateway. In the past it has sometimes been the date, and other times there have been filters, either on the Visual, Page or Report Level Filters. So if you can confirm that there are no filters?
Getting closer, I checked out the refresh history and yes, it has kicked off a manual refresh. Every day you learn something new!
There are visual level filters but only one is doing anything and it's the same in both.
However what you said got me thinking, it could be that 365 is working with US date format and desktop UK, that could explain the difference?
There as field in particular with the following DAX and it's the only filter;
Active2017 = AND(Ramtech_Active_Contracts[Start_Date]<date(2018,4,1), Ramtech_Active_Contracts[End_Date]>=date(2017,4,1))
Could this be calculated differently based on language settings?
Anyway, I'll spend some time this morning working with the original view to see what the numbers should be and that may give me a clue too.
Many thanks for you help,
Dave
Hi Stormkahn
Possibly could you just put in a simple measure that does a SUM or COUNT into a table.
Then upload this to the Power BI Service, and after the refresh see if the values are the same?
By doing this we can evaluate if the measures are possibly the source of the issue or not.
Quick update, priorities are elsewhere at the momemnt so only managed 15mins yesterday to follow up.
If I query the view directly in the db and filter based on dates I get numbers very similar to those experienced;
(CONVERT(datetime,Contract_Term_Date,103)>=CONVERT(datetime, '04/01/2017', 103)) generates 69 records
and
(CONVERT(datetime,Contract_Term_Date,103)>=CONVERT(datetime, '01/04/2017', 103)) generates 9 records (correct value)
That's as far as I'd got yesterday but it does look like we're on the right track and it's that old US vs UK dates chesnut.
I'm hoping to get some time later,
cheers,
Dave