
1. Re: Commodity Futures Price Full Year Average
Michel Caissie Dec 6, 2018 9:32 AM (in response to josh McFarlane)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 contractMonthperiodDay)])}
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

futures demo  Forum(mc).twbx 153.4 KB


2. Re: Commodity Futures Price Full Year Average
josh McFarlane Dec 6, 2018 2:14 PM (in response to Michel Caissie)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.