Friday, March 23, 2012

Number of remain periods (Quarters, Months)

I will need to figure out how many remaining periods (remaining quarters, months) within a fiscal year. For example, today is still Jan, 07. So there will be 3 quarters remaining if my current member on time dimension is at quarter level. There will be 11 months remaining if my current member on time dimension is at month.

I need that numeric value in order to do some calculation.

How can I get that number?

Here's a sample Adventure Works query (only works below fiscal year level):

>>

with

Member [Measures].[YearEndCount] as

count({[Date].[Fiscal].NextMember:

ClosingPeriod([Date].[Fiscal].CurrentMember.Level,

Ancestor([Date].[Fiscal].CurrentMember,

[Date].[Fiscal].[Fiscal Year]))})

select {[Measures].[YearEndCount]} on 0,

Head(Descendants([Date].[Fiscal].[Fiscal Year].&[2003],

[Date].[Fiscal].[Fiscal Year], AFTER), 5) on 1

from [Adventure Works]

--

YearEndCount
H1 FY 2003 1
Q1 FY 2003 3
July 2002 11
July 1, 2002 364
July 2, 2002 363

>>

No comments:

Post a Comment