365 data inconsistent with the desktop

[Stormkahn]

Hi All,

I’ve taken some time to investigate a peculiar issue but thus far have found nothing, my search foo has also failed me.

One of the users questioned the some of the figures in a shared report.

The report was developed in the desktop version and published to Office 365 and shared. The data is being regularly refreshed from an on premises gateway.

If I load up the report in on the desktop and refresh the data I get a different set of figures, that’s fine insomuch as the data is up to the minute fresh whereas the published version is up to 2 hours out of date. The numbers are so significantly different that the time lag doesn’t explain it.

I’ve re-published the report including the dataset and then reviewed in 365 to find the numbers once again match, but only briefly. Within a few minutes the 365 data seems to revert to the “other data”, given the dataset refresh schedule it’s not simply that it’s kicked in and overwritten it.

I’ve upgraded both the desktop and the gateway to the latest versions.

I’ve reviewed every setting, log and diagnostic I could find to see if anything was wrong and found nothing at all. There’s nothing being reported on the gateway’s server, the 365 connections are all working ETC ETC.

I’ve double and triple checked all the data sources/datasets in desktop and in 365 to make sure it’s the same db/server ETC.

The only thing I’ve not done and completely remove the report ETC from 365 and re-publish from scratch.

Hopefully I’ve described the issue well enough? Any clues?

Many thanks in advance,

Dave.

[Stormkahn]

OK, so I've followed up with some more investigating and pulled the data directly. A "manual" count of the source data confirms that the desktop version is generating the correct values.

The 365 version is generating values that I would expect to see using the US date formating.

So since 4th Jan 2017 (4/1/2017 US) we've had 61 contracts terminated and from 1st Apr (1/4/2017 UK) we've had 9.

The report is based on financial years running Apr to Mar so 9 is the expected value.

Now I've not found a way of reviewing the data in 365 to see if the gateway has supplied the dates in US format or if the calculated fields are re-formatting the values.

There are 3 relevant calculated columns that provide the values for the count;

Start Date (FY) = IF(MONTH(Ramtech_Active_Contracts[Start_Date])<4,
	YEAR(Ramtech_Active_Contracts[Start_Date])-1,
	YEAR(Ramtech_Active_Contracts[Start_Date]) 
)

End_Date = IF(ISBLANK(Ramtech_Active_Contracts[Contract_Term_Date]),
	Ramtech_Active_Contracts[Contract_End_Date],
	Ramtech_Active_Contracts[Contract_Term_Date]
) 

Active2017 = AND(
	Ramtech_Active_Contracts[Start_Date]<date(2018,4,1), 
	Ramtech_Active_Contracts[End_Date]>=date(2017,4,1)
) 

The final numbers are in a matrix of count of contracts by current status (we're looking at terminated here) were Active2017=True so there's a visual level filter.

The next next thing I'll check is the server side settings, watch this space.

cheers,

Dave

[Stormkahn · Accepted solution]

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

[GilbertQ]

Hi there

Do you have dates and times in your dataset?

If so what timezone are you in? 

The reason that I ask is that all the servers are in UTC so if you have data that uses Date and Time it will then change from your desktop which could be in UTC+8 whilst in the Power BI Service it is UTC. 

So this will mean that in the desktop at 6AM local time it is showing the data for today.

Whilst once it is uploaded and being refreshed in the Power BI Service that same data would be showing up as yesterday.

[Stormkahn]

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

[GilbertQ]

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?

[Stormkahn]

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

[GilbertQ]

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.

[Stormkahn]

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