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