5 Replies Latest reply on Jul 14, 2016 12:51 AM by Shinichiro Murakami

# Full Prior Year Independent of Date Filter

I am trying to create a report that will show YTD Sales and Budget for the current year and in a separate column show the Full Prior Year sales. I have the following formula, but it returns nulls. Can anyone help?

If [Inv Date] >= DATEPARSE('mm/dd/yyyy','01/01/2015') and [Inv Date]  <= DATEPARSE('mm/dd/yyyy','12/31/2015') then [Sales] ELSE 0 end

This is what I trying to achieve

• ###### 1. Re: Full Prior Year Independent of Date Filter

hi Marlon

try to use zn(sales)

• ###### 2. Re: Full Prior Year Independent of Date Filter

This article did not help, it only gave me YTD or YOY which I know how to calculate. What I am looking for is a way to show the Full Prior year (all 12 Months), prior YTD (6 months) and current YTD (6 months). When I apply a filter to get the first 6 months I loose the prior year 12 month view. Any help would be ap[reciated.

• ###### 3. Re: Full Prior Year Independent of Date Filter

Marlon,

I think you can put independent calculated field to get expected results?

ee

[year 2015 act YTD]

if [Year]=2015 and [Date] <= dateadd('year',-1,today()) then [Actual] END

[year 2016 budget YTD]

if [Year]=2016 and [Date] <= today()then [Budget] END

[Var to Budget]

sum([year 2016 act])-sum([year 2016 budget YTD])

[Var to last year]

sum([year 2016 act])-sum([year 2015 act YTD])

Thanks,

Shin

9.2 attached

• ###### 4. Re: Full Prior Year Independent of Date Filter

This helped, thank you.

• ###### 5. Re: Full Prior Year Independent of Date Filter

If you are O.K. could you mark my answer as "correct".

Shină€€