Create calculated member in SSAS using MDX

Question

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.

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 :

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

Please help

MoazRub MoazRub · Accepted Answer · 2019-02-16T16:14:19

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]

Create calculated member in SSAS using MDX

1 Answers