CODEWITHSUNDEEP
ssasolapolap-cube
Scott Thornton
Scott Thornton

Reputation: 136

Is it possible to create a dynamically calculated field?

How can I create a dynamically calculated field to determine the age in days based on a date/time stamp?

For example say I have a field that has a date time stamp. What I want to be able to see when browsing the cube is a column showing the number of days since the time stamp. So if the date time stamp is '8/21/13 12:00PM' and I browse the cube on '8/22/13 12:00PM', the calculated column "Number of Days Since" would show 1.

Is this even possible with SSAS?

Upvotes: 1

Views: 1293

Answers (1)

FrankPl
FrankPl

Reputation: 13315

Yes, you can:

with member Measures.[date string] as
            Left(Right([Date].[Date].CurrentMember.UniqueName, 9), 8)
     member Measures.[date formatted] as
            Left(Measures.[date string], 4) + "-" +
            Mid(Measures.[date string], 5, 2) + "-" +
            Right(Measures.[date string], 2)
     member Measures.[date date] as
            CDate( Measures.[date formatted])
     member Measures.[to today] as
            now() -  Measures.[date date]
            , format_string = 'yy" years, "m" months, "d" days"'

select { 
          Measures.[date string],
          Measures.[date formatted],
          Measures.[date date],
          Measures.[to today]
       }
       on columns,
       [Date].[Date].[Date].Members
       on rows
  from [Adventure Works]

gives what you want. You do of course not need all the intermediate measures, they just show step by step what is calculated.

One remark, however: I formatted the to today measure with a date format using yy. A four digit year would show as "1912 years" etc, as SSAS is internally using the date coding like Excel of number of days since Jan, 1, 1900. I mis-used the date formatting here to format a duration, which is a number and not a real date. This formatting approach is only giving correct results for positive durations less than 100 years. But this is only a formatting issue and the main duration calculation is correct nevertheless. It just shows the duration in days, and the time within the day as fractions.

Upvotes: 1

Related Questions