PBIX file size and datetime field with 9999 as year

[cpmohanraj]

I recently started using Power BI and have observed the following behavior. I just want to understand the cause for this behavior.

I have created a report using desktop which brings back ~2k rows from SQL Server. The query has 6 columns. The query has VALID_FROM and VALID_TO date. The resulting PBIX file with this query is ~26 MB. I played around the query and understood that VALID_TO date field which has most the value of 31/12/9999 as the value is the root cause for the large size of file.

As soon as I remove the field from the query or change the query to replace 31/12/9999 with 31/12/2099, the file size comes down to ~360 KB. That's massive difference in file sizes. 

I'd love to understand the reason for this.

[Anonymous]

Dates are stored as a number as "Days from 31/12/1899".  Therefore a date in 9999 would be a far larger number, which would take up much more space in the database.

[cpmohanraj]

Thanks Ross! 

But the 9999-12-31 is just 2,958,464 days away from 31/12/1899, which is not a large number.

Could it be bacause my dates have time component, assuming it may be storing them as seconds from the same reference date? 

[Anonymous · Accepted solution]

Time components are stored as a decimal.  So yes that would increase the amount of space required to store the floating point value.