Previous YTD

[tomgao]

Hi all

I am trying to perform Pervious Year To Date Total (which is like YTD but for the previous year. ie. ytd would work for this year's jan -> whatever month. But I need a total from Last year from jan -> whatever year to do comparision), However I've ran into the following issues.

Since Year is a variable and it can change depending on which year you select. I tried to Return the first member of the month which in theory should be January of a Year. However when I performed the below query I got Dec which was the overall period through out the entire Month this include 2002 dec -> 2005 May period so the head returned Dec.

SELECT { head(Descendants(
[Period].CurrentMember,
[Month]),1)} ON COLUMNS , { [BrandModel].[All BrandModel] } ON ROWS FROM [Running Report] WHERE ( [Measures].[Unit] )


So then I tried to return the Head of the Set of the current year using the below query. But no matter what I do I can not get it working.

SELECT { head(Descendants(
{[Period].CurrentMember, [Period].[Month]},
[Month]),1)} ON COLUMNS , { [BrandModel].[All BrandModel] } ON ROWS FROM [Running Report] WHERE ( [Measures].[Unit] )

How would you guys usually do a previous year to date comparision ? since year is a variable

Thanks
Tom

 

[DaOtH]

Tom:

Play around with DateAdd and Convert,

Try something like :

DECLARE @charYTDStart char(8)
DECLARE @charYTDEnd char(8)

SELECT @charYTDStart = LEFT(CONVERT(char(8),DATEADD(Year,-1,GETDATE()),112),4) + '0101'
SELECT @charYTDEnd = CONVERT(char(8),DATEADD(Year,-1,GETDATE()),112)

"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963

 

[Flybridge]

Use ParallelPeriod. e.g.

Sum(YTD(ParallelPeriod(Year,1,Time.CurrentMember),[Measures].[MeasureName]))

'Year' is the variable we are concerned with, '1' is the offset i.e. last year