Search code examples
ssasmdx

Create calculated member in SSAS using MDX

To All MDX gurus, I have a scenario of creating a member in SSAS. There is a fact table having the snapshot of data for each day. enter image description here

2018Q1 is from Dec to Feb. The requirement is to create a calculated member that will give the distinct count of value of last day snapshot of the Q1 2018 if sliced on Q1 2018 i.e. the distinct count of value for 20180228(last day of A1 2018) and Similarly this should work on the last day of month on slicing on month level vice versa for week and year. I have calendar hierarchy in the date dimension.

The final result set should be like :

enter image description here

I have already created the distinct count of value in measure but that will work only on date level.

Please help

Solution

  • You need to use descendants function with Self flag. Take a look at the sample below

    with member measures.t 
as 
(Descendants  ([Date].[Calendar].currentmember,[Date].[Calendar].[Date],SELF).item(Descendants ([Date].[Calendar].currentmember,[Date].[Calendar].[Date],SELF).count-1).item(0)
,[Measures].[Internet Sales Amount])

member measures.t1 
as 
Descendants  ([Date].[Calendar].currentmember,[Date].[Calendar].[Date],SELF).item(Descendants ([Date].[Calendar].currentmember,[Date].[Calendar].[Date],SELF).count-1).item(0).name

SELECT {measures.t ,measures.t1}
ON 0 ,
[Date].[Calendar].[Calendar Year].&[2013]
on 1 
FROM [Adventure Works]