2 Replies Latest reply on Dec 6, 2018 2:14 PM by josh McFarlane

# Commodity Futures Price Full Year Average

I am using 2018.2.0 Tableau

I am working with a data set of commodity futures.  The work book I have uploaded has 2018 prices each day from June 1 2017 through November 26 2018.  Each data point represents the price for delivery in a certain 'Contract Month'  as of a specific 'Report Period'.  As I move forward in time, some 'Contract Months' become actualized and drop out of the data.  The last data point in the database for a given 'Contract Month' is the settlement price for that month.

I am trying to calculate the average price for Jan-Dec 2018 on each 'Report Period'.  Early in the time period, this is just the average of 'Close Price' for each 'Contract Month' on a specific 'Report Period'.  As I move forward in time, I need to calculate an average based partially on settlement prices and partially on future 'Close Prices'

For example, on March 1, 2018, the average should be calculated with Jan & Feb settlement prices of 2.20 & 2.11, and March-December futures prices.

Is there a way to do this with a formula?  Or is there a way to make the last data point in this table to fill down to the bottom of the table?

I had tried formulas with ISNULL - but it did not work since the blank data points are not null, they just dont exist in the database.

Josh

• ###### 1. Re: Commodity Futures Price Full Year Average

Josh,

I hope I understood the requirement correctly.

I didn't find an easy way to do it. I checked first by using table calcs, but the computing would require a bunch of dimensions in the detail of the final view, and it would get complicated, if possible, to get the desired view.

I finally got the numbers  using  a bunch of lods.

Check on sheet ContractYearAvg  if you have the correct numbers. Note that if you do the math with a calculator using the two decimals values you have on the Monthly sheet, you will have slightly different values , since i didn't round anything in the various calcs.

Step 1 is to get the latest values that we will use for the empty months

First I get the LastReportPeriodWithData  using

{FIXED DATEPART('year', [Contract Month]),DATEPART('month', [Contract Month]): MAX( [Report Period] )}

Next I get the Close Price conversion avg  for those days with

{FIXED [Product Name],DATEPART('year', [Contract Month]),DATEPART('month', [Contract Month]),DATEPART('year', [Report Period]),DATEPART('month', [Report Period]),DATEPART('day', [Report Period]):

AVG( if [Report Period] = [LastReportPeriodWithData]  then [Close Price Conversion] end )

}

Next I make a calculation for each month returning this  last  Close Price Conversion avg  using

{FIXED [Product Name]:MIN( if DATENAME('month', [Contract Month]) = 'January' then [Close Price Conversion (avg on last day)] end )}

Step 2 is to compute the Contract Year average.

To do so, I count the number of month with data  for a given Period day,  I sum those  averages , I add the missing month averages, and I divide by twelve.

So the number of month with data would be

{FIXED [Product Name],DATEPART('year', [Report Period]),DATEPART('month', [Report Period]),DATEPART('day', [Report Period]):COUNTD( DATEPART('month', [Contract Month]))}

I will need the avg  Close Price Conversion per contract month  , per Period day with

{FIXED [Product Name],DATEPART('year', [Contract Month]),DATEPART('month', [Contract Month]),DATEPART('year', [Report Period]),DATEPART('month', [Report Period]),DATEPART('day', [Report Period]):

AVG([Close Price Conversion])

}

the sum of those months averages with

{FIXED [Product Name],DATEPART('year', [Report Period]),DATEPART('month', [Report Period]),DATEPART('day', [Report Period]):SUM([Close Price Conversion (avg per  contractMonth-periodDay)])}

and  finally  the  Contract Year average with

(

case [nb contract month with value]

when 2 then

[LastValueJanuary] +

[LastValueFebruary] +

[LastValueMarch]+

[LastValueApril]+

[LastValueMay]+

[LastValueJune]+

[LastValueJuly]+

[LastValueAugust]+

[LastValueSeptember]+

[LastValueOctober]

when 3 then

[LastValueJanuary] +

[LastValueFebruary] +

[LastValueMarch]+

[LastValueApril]+

[LastValueMay]+

[LastValueJune]+

[LastValueJuly]+

[LastValueAugust]+

[LastValueSeptember]

when 4 then

[LastValueJanuary] +

[LastValueFebruary] +

[LastValueMarch]+

[LastValueApril]+

[LastValueMay]+

[LastValueJune]+

[LastValueJuly]+

[LastValueAugust]

when 5 then

[LastValueJanuary] +

[LastValueFebruary] +

[LastValueMarch]+

[LastValueApril]+

[LastValueMay]+

[LastValueJune]+

[LastValueJuly]

when 6 then

[LastValueJanuary] +

[LastValueFebruary] +

[LastValueMarch]+

[LastValueApril]+

[LastValueMay]+

[LastValueJune]

when 7 then

[LastValueJanuary] +

[LastValueFebruary] +

[LastValueMarch]+

[LastValueApril]+

[LastValueMay]

when 8 then

[LastValueJanuary] +

[LastValueFebruary] +

[LastValueMarch]+

[LastValueApril]

when 9 then

[LastValueJanuary] +

[LastValueFebruary] +

[LastValueMarch]

when 10 then

[LastValueJanuary] +

[LastValueFebruary]

when 11 then

[LastValueJanuary]

else 0

end

+

[Close Price Conversion (SUM per day of report)]

)

/12

Check  the sheets  ValidateData1, ValidateData2  and  ContractYearAvg

Michel

• ###### 2. Re: Commodity Futures Price Full Year Average

Thank you Michel,

Your solution worked.  Thank you for the work you put into it, it was not an easy solution so I appreciate it.