How to find the quarter,year,month,semester start and end dates

0
votes

I have a date dimension set in the SSAS Cube. Below is the screenshot attached.enter image description here

I have been trying get quarter,year,month,semester start and end dates using ClosingPeriod() and OpeningPeriod() functions but not getting the exact value. How the get correct dates for a given date.

2
datessasmdxcube
Do you have an example of a script that you have written using this dimension that does not behave as you'd expect it to behave? – whytheq

2 Answers

0
votes
Syntax ClosingPeriod( [ Level_Expression [ ,Member_Expression ] ] )

You have to add calendar members ON 1. Example

WITH MEMBER [Measures].[X] AS
( 
ClosingPeriod (
[Time].[Year Quarter Month].[Year Name],
[Time].[Year Quarter Month].CurrentMember
),
[Measures].[A]
)
SELECT [Measures].[X] ON 0,
[Time].[Year Quarter Month].[Year Name].Members ON 1
FROM [ABC]
0
votes

I have month sorted by month name instead of the month number. After correction following query is working fine.

SELECT 
{
    OpeningPeriod([Dim Time].[Calendar].[Date],[Dim Time].[Calendar].[Date].&[2014-08-18T00:00:00].Parent.Parent.Parent.Parent)
} ON 0,
{
    [Measures].[Ending Market Value Base] 
} ON 1
FROM [Cube]