5 Replies Latest reply on Nov 14, 2019 3:24 PM by swaroop.gantela

# Calculate YTD and MTD returns from price series

Hi All,

I hope this message finds you well.

My question should be simple to answer but I've already spent some time trying to do it and yet didn't manage to figure this one out.

I have a database basically organized like this:

DateAssetPrice
01/01/2018FOO1256.234331
01/01/2018BAR1156.22145

And what I need is to calculate the YTD, MTD and Day-to-date returns for that time series, creating an output like this one:

Asset1dMTDYTD
FOO0.33%2.44%157.33%
BAR0.15%1.23%87.58%

It's worth noticing that this returns should be calculated by doing (Price Today - (Price in the Last day From Previous Period [day, month, year])/(Price in the Last day From Previous Period [day, month, year]).

For example, if I am talking about year-to-date today (2019-11-08) I should do price today - price in 2018-12-31/ price in 2018-12-31.

Also, I do not want to plot dates in my views. Just the value for 1d, MTD and YTD from TODAY, as shown in the second table.

Can anyone help me with this one?

Thank you, very much.

• ###### 1. Re: Calculate YTD and MTD returns from price series

Joao,

I think it should be doable with DATETRUNC calculations.

-you have a price for everyday?

-you are looking to see the difference from today's price using the TODAY() function?

If so, then today's price is:

IF [Date]=TODAY() THEN [Price] END

And the price at the last day of the previous year is:

IF [Date] = DATEADD( 'day', -1, DATETRUNC( 'year', TODAY() ) ) THEN [Price] END

Cannot subtract these two because they are on different rows, so need to fix across the whole asset.

You can use a Level of Detail calculation (Overview: Level of Detail Expressions - Tableau )

as for example today's price:

{ FIXED [Asset] : MAX( IF [Date]=TODAY() THEN [Price] END ) }

Likewise fix the last year price.

Then the YTD will be just the ratio that you described.

Similarly for MTD and 1D.

1 of 1 people found this helpful
• ###### 2. Re: Calculate YTD and MTD returns from price series

Hi, Swaroop.

That is a great solution. Still, I have a few questions about it.

Is it really necessary to create an entire column with the same numbers? That sounds like a canon shot to kill an ant.

And the only catch here is that your first assumption is not true, (about having a price for each day) as I only have prices for workdays, how can I edit the formula to not get the last day - 1 but the last day - 1 if not null?

Edit: If it helps, I am attaching the database. Note that the US Asset data series is complete while the other assets are just defined in workdays.

Thank you!

• ###### 3. Re: Calculate YTD and MTD returns from price series

Joao,

Sorry for not clarifying, the extra column was only for demonstration purposes.

The Level of Detail calculation allows for just the final result as in sheet "summary table" of the attached.

With regards to the missing days of data, I think that can be handled programmatically.

Today is 11/12/2019.

So the Target Date for last year is

which will return 12/31/2018

In the fake dataset attached (sorry I didn't open your dataset yet),

the only dates with price data are 11/27/2018, 11/28/2018, and 1/2/2019.

So I am assuming that you are wanting the 11/28/2018 price.

That is done by first finding the difference between all dates and the target date:

DATEDIFF('day',[Date],[Target Date])

Then finding the maximum date with a non-negative difference:

{MAX(IF [Difference from Target Date]>=0 THEN [Date] END)}

Then to get the Last Year Price it is:

IF [Date]={MAX(IF [Difference from Target Date]>=0 THEN [Date] END)}

THEN [Price]

END

Those calculations are laid on the sheet called "date difference" of the attached.

1 of 1 people found this helpful
• ###### 4. Re: Calculate YTD and MTD returns from price series

Hi,

This method works just fine.

There are some limitations with my database, for example:

• One of the assets contains every day and the others only workdays
• Each asset starts and ends in different dates (the end dates I managed to fix, just use instead of today() {fixed assets: max(date)}

But this is just fine for now.

Thank you!

• ###### 5. Re: Calculate YTD and MTD returns from price series

Joao,