Calculated member to get dimension value in previous year

1
votes

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.

1
ssasmdxcube
(upped your question as I'm interested to try and help) – whytheq

1 Answers

1
votes

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])