Create Measure for DateTime DataType in SSAS

*** Self reference entry by Liyasker **
There is a requirement to create the Measure for User InTime Hrs, OutTime Hrs, CheckInTime, CheckOutTime, AVGCheckInTime with HH:MM AM/PM format.

But SSAS measure will support only for Integer and Numeric. Our case I have to expose the cube measure in to MS EXCEL 2007. There we can’t do any conversion.

I got the right help from the following blog of John (Thanks John :)). Please go through this, this will help you.

http://sqlblogcasts.com/blogs/drjohn/archive/2007/12/03/ssas-working-with-date-and-time-measures-to-calculate-average-elapse-time.aspx

I create a Facttable view and did the float conversion like below
,CONVERT(FLOAT,CAST(InTime_InHrs AS DateTime)) + 2 AS OfficeInTime
,CONVERT(FLOAT,CAST(OutTime_InHrs AS DateTime)) + 2 AS OfficeOutTime
,CONVERT(FLOAT,CheckInDateTime) + 2 AS InDateTime
,CONVERT(FLOAT,CheckOutDateTime) + 2 AS OutDateTime

Then I apply the Time format in SSAS Measure Properties.

I hope this will help to calculate DateTime Measure in SSAS.