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

Stormkahn's avatar
Stormkahn
Regular Visitor
8 years ago

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