Tableau - Calculation based on last value per month

Question

My dataset is a series of daily snapshots of a fixed income index. In each daily snapshot is the month to date return of each security. I need a way of taking the end of month MTD return for each month and then multiplying them to reach YTD returns. Is anyone able to help? Apologies as slightly awkward to explain.

Andy TAR Sols Andy TAR Sols · Accepted Answer · 2020-07-23T17:59:08

You are able to isolate the end of month only using a fixed calc. Something like:

{FIXED DATETRUNC('month',[DateField])): MAX([DateField])}

You may need to play around with the date in the fixed, you may need to fix by month and year of date instead. Anyway, once that is working then create the following to keep only the month ends:

[DateField] = {FIXED DATETRUNC('month',[DateField])): MAX([DateField])}

Put this to the filters and set to True, then calculate your YTD using those values.

Alternatively use the above in an IF statement:

IF [DateField] = {FIXED DATETRUNC('month',[DateField])): MAX([DateField])} THEN [YTD calc] END

Tableau - Calculation based on last value per month

1 Answers