PeriodsToDate in a flat time dimension -
04-08-2005
, 07:18 AM
My time dimension is a flat month dimension because my measure is not
additive across months. And still, I want a YTD measure. I'll explain
the details of my month dimension and describe what I've attempted.
Please help me refine my approach or suggest a new one. MANY THANKS!
Actually, my need is a little more specific than a YTD summary. Because
the company's fiscal year starts in April, my flat list of months in
the dim is
Jan FY04
Feb FY04
Mar FY04
Apr FY05
May FY05
Jun FY05
Jul FY05
.... and so on.
I want a calculated measure that will sum from [Apr FY05] to the
present month. We don't have data before [Jan FY04] so I'd rather just
leave the calculated measure = 0 for those months. Here's what I've
tried so far:
IIf(
<Condition if month < Apr FY05>,
0,
Sum([Month].[Apr FY05]:[Month].CurrentMember , [Measure]
)
I cannot come up with a good condition that identifies the months
previous to my beginning month. If feel silly about that . I also
wonder if there is another function that will do a better job here
rather than rolling my own YTD function. |