6 Replies Latest reply on Feb 7, 2019 2:32 AM by ewa.mckillop

# YTD, Previous YTD, etc calculation

Hello

I'm trying to create a calculation to be able to get to Year to Date, Year to date Last Year , Year to date 2 Years Ago etc based on the selected date.

Example: When I select 29/09/2018 (from parameter 'Choose a date').

I will get a table with: Year To Date (which is 01/01/2018-29/09/18), YTD Last Year (01/01/2018-29/09/2017) and YTD Two Years Ago (01/01/2016-29/09/2016) .

I should mention that the raw data is cut weekly, so it won't ideally align to 29/09 every year but it should be the closest date to that.

I've done this calculation but something is missing:

STR([Choose a date] <= MAX([Date]) AND

DATETRUNC( "year", [Choose a date]) = DATETRUNC("year", max([Date]) ))

• ###### 1. Re: YTD, Previous YTD, etc calculation

Hi EWA,

Thanks

Deepak

• ###### 2. Re: YTD, Previous YTD, etc calculation

See attached a sample file.

I'd like to get to YTD similar structure (i.e. columns) to what I have in MAT (Moving average tab).

Columns with:

YTD, YTD Last Year, YTD 2 Years Ago, YTD 3 Years Ago...

Thanks

Ewa

• ###### 3. Re: YTD, Previous YTD, etc calculation

Depending upon Date you Choose in Parameter, you will always have Null Column, Which You Can give Some Name.

Thanks

Deepak

If It Helps, Pl Mark it Helpful and CORRECT to Close Thread

• ###### 4. Re: YTD, Previous YTD, etc calculation

HI Deepak, that's brilliant , in fact, almost exactly what I need!

I just noticed a couple of things:

1. YTD  is not working as it gives a full YTD up to the latest date available rather than to the date specified in "Choose the date"

2. Would it be possible to do calculation by the number of weeks from the beginning of each year so that, for example: if I choose 25/08/2018 which is the 34th week of 2018 (counting from 01/01/2018), YTD Last year will also be the 34th week (counting from 01/01/2017)? I'm not sure if this will work for 2015 as I have partial year data though? Otherwise, I noticed that I might miss one week worth of data.

Thanks a lot for your help

Ewa

• ###### 5. Re: YTD, Previous YTD, etc calculation

Hi Deepak, I was wondering if you've had a chance to look at the above question? Thanks a lot

• ###### 6. Re: YTD, Previous YTD, etc calculation

create a filter (calculated field)

=DATEPART("week",[Date]) <= (DATEPART("week",[Choose a date]))

then filter only on 'True'

Thanks