Forum Discussion

erhodes's avatar
erhodes
Advocate II
9 years ago

Analyze in Excel - Date field issue

When using 'Analyze in Excel' Date fields from my model show up in excel as text which really limits the usage of the Analyze in Excel functionality. Has anyone else encountered this and come up with...
General Comment
CLEG's avatar
CLEG
New Member
2 years ago

It is quite simple: In your Dataset (Semantic Model) , in your calendar, create a column DateSerial = VALUE ( FORMAT ( Calendar[Date],"00000" )).  Then in your Dataset (Semantic Model) measures, add a measure DateSerial = Max ( Calendar[DateSerial] ).  When you pull your data into Excel (Analyze in Excel), pull both value into your "pivot": Calendar[Date] and the measure [DateSerial].  Very important to bring the measure because Calendar[DateSerial] will be a string.  And very important to bring the Calendar[Date] as well because even though it is a string and useless, it defines the date granularity required to show the measure DateSerial properly.