DAX SUMMARIZE() changes datatype of <GroupbyColumn> from Date to DateTime

[Mixednuts]

Any ideas why the DAX SUMMARIZE() table function should change the data type of a Date column used in the GroupByColumns parameters to a DateTime data type in the resulting table? 

For example, a column[Start Date] of type 'Date' with a value '1/6/2018' when used in the GroupByColumns list changes to type 'DateTime' with a value '1/6/2018 12:00:00 AM'

I wonder why the data typing isn't preserved?

I know I can explicitly reset the datatype, but I would have thought it should not be necessary. 

[v-diye-msft · Accepted solution]

Hi @Mixednuts 

We have got the confirmation from EG team:

It's by design. I found this page: https://docs.microsoft.com/en-us/power-bi/desktop-data-types but the section on date/time is not clear. I have updated the docs (should be published in the next 24 hrs). 

It'll be updated as follows. highlighted the relevant sections.

Date/time types

Power BI Desktop supports five Date/Time data types in Query View. Both Date/Time/Timezone and Duration are converted during load into the model. The Power BI Desktop data model only supports date/time, but they can be formatted as dates or times independently.

Date/Time – Represents both a date and time value. Underneath the covers, the Date/Time value is stored as a Decimal Number Type. So you can actually convert between the two. The time portion of a date is stored as a fraction to whole multiples of 1/300 seconds (3.33 ms). Dates between years 1900 and 9999 are supported.

Date – Represents just a Date (no time portion). When converted into the model, a Date is the same as a Date/Time value with zero for the fractional value.

Time – Represents just Time (no Date portion). When converted into the model, a Time value is the same as a Date/Time value with no digits to the left of the decimal place.

Date/Time/Timezone – Represents a UTC Date/Time with a timezone offset. It’s converted into Date/Time when loaded into the model. The Power BI model doesn't adjust the timezone based on a user's location or locale etc. If a value of 09:00 is loaded into the model in the USA, it will display as 09:00 wherever the report is opened or viewed.

Duration – Represents a length of time. It’s converted into a Decimal Number Type when loaded into the model. As a Decimal Number type it can be added or subtracted from a Date/Time field with correct results. As a Decimal Number type, you can easily use it in visualizations that show magnitude.

[Mixednuts]

Thank you Dina,

That was informative. Just a clarification? For Date/Time/Timezone data types you say the model does not adjust these to the user's locale. I assume this is not in reference to Power Query whose functions will convert to local time where ever a Date/Time/Zone is expected and a Date or Date/Time is supplied?

I understand the internal distinction between Dates and Date/Times being Whole numbers v Fractionals and I understand that it is more about formatting than storage, however I still find it curious that even though the groupby column's format is known, it cannot be carried through? 

I do appreciate the documentation being updated. Thanks!

[v-diye-msft]

Hi @Mixednuts 

Thanks for catching this. I have reported it to the internal team for better explanation. will keep you updated.