3 Replies Latest reply on Dec 16, 2016 9:27 AM by Hannah Forristall

# Creating a PY QTD Measure

Hi,

I'm trying to create a prior year QTD measure and I'm having trouble getting it to work. This is the formula I am using for the prior year YTD measure:

sum(if YEAR([Date])= YEAR([Reference Date])-1 and

[Date]<= DATEADD('year', -1,[Reference Date])then [Lbs] end)

Can anyone advise me on how to change the formula so that it will work for QTD?

Thanks so much,

Hannah

• ###### 1. Re: Creating a PY QTD Measure

Hi Hannah,

It's pretty tough to tell you why it isn't working without seeing the data.

• ###### 2. Re: Creating a PY QTD Measure

hi Hannah,

So very similar to the way you've created your YtD, we can add some extra checks using the DATEPART function...

In the attached, I've created a parameter (your reference date) which is what controls where we are in QtD...

So first I created a measure for the current QtD

[Sales - Current QtD]

IF YEAR([Order Date])= YEAR([Reference Date]) AND

DATEPART('quarter', [Order Date]) = DATEPART('quarter', [Reference Date])

THEN [Sales]

END

and then I created the following for QtD PY (btw I've used Week level, but you could use DATEPART with 'DayofYear', or any other date part depending on how you define the QtD)

[Sales - PY QtD]

IF YEAR([Order Date])= YEAR([Reference Date])-1 AND

DATEPART('quarter', [Order Date]) = DATEPART('quarter', [Reference Date]) AND

DATEPART('week', [Order Date]) <=DATEPART('week', [Reference Date])

THEN [Sales]

END

In the attached, the "how it works" tab lets you see how they are calculating.

You might also find this post handy...Current vs Previous Period to Date Comparison

Hope that helps, but let me know if it's not doing what you need.

• ###### 3. Re: Creating a PY QTD Measure

This worked great Simon!

Thank you both for your help!

Hannah