Forum Discussion

Stormkahn's avatar
Stormkahn
Regular Visitor
8 years ago
Solved

365 data inconsistent with the desktop

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 re...
  • Stormkahn's avatar
    Stormkahn
    8 years ago

    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's avatar
GilbertQ
Super User
8 years ago

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's avatar
Stormkahn
Regular Visitor
8 years ago

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