YTD Sum of last day in month.

#1

Hi,

My report will run on the last day of every month and display MTD and YTD
figures. For one measure though the YTD must only sum the last day in every
month.
For example on the 30/09/2005, I need to sum the measure's value for the
last day in every month from Jan to Sep.
My date dimension consists of Year->Qrt->Month->Week->Day.

Thanks
BvR

#2

Maybe something like:

Quote:

Sum(Generate(YTD(Ancestor([Date].CurrentMember,

[Date].[Month])), Tail(Descendants([Date].CurrentMember,
[Date].[Day])), [Measures].[ClosingVal])

Quote:

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

#3

Thanks Deepak, that works fine.
"BvR" wrote:

Quote:

Hi,

My report will run on the last day of every month and display MTD and YTD
figures. For one measure though the YTD must only sum the last day in every
month.
For example on the 30/09/2005, I need to sum the measure's value for the
last day in every month from Jan to Sep.
My date dimension consists of Year->Qrt->Month->Week->Day.

Thanks
BvR

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

YTD Sum of last day in month.

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

YTD Sum of last day in month. - 01-18-2006 , 09:41 AM

Re: YTD Sum of last day in month. - 01-18-2006 , 06:31 PM

RE: YTD Sum of last day in month. - 01-19-2006 , 04:10 AM