Search code examples
ssasmdxcube

Calculated member to get dimension value in previous year

I am new to BI and need help to create a calculated member.

I have a cube with following dimension and measure:

  • Dimension tables: Dim_Time (Year, Month, Day)

  • Dim_MyValueCollection: DataType with data types having values 'Gross Paid Claims' and 'Gross Written Premiums'

  • Measure: FactTable.Value

I need to create a calculated member on Dim_MyValueCollection.DataType to represent the difference of 'Gross Written Premiums' values in current and previous year.

Solution

  • To get data from a previous period in mdx you can use these functions:

    Lag

    https://msdn.microsoft.com/en-us/library/ms144866.aspx

    ParallelPeriod

    https://msdn.microsoft.com/en-us/library/ms145500.aspx

    Have you attempted any mdx ?

    Edit

    Here is a general tuple for the change in Gross Written Premiums since the same period in the previous year. I've guessed the names of your dimensions because you have not supplied any sample mdx: 

    ([Time].CURRENTMEMBER, [Measures].[Gross Written Premiums]) 
-
(PARALLELPERIOD(Year,1,[Time].CURRENTMEMBER), [Measures].[Gross Written Premiums])