Data has multiple companies production data for a month, this includes a column for actuals and a column for forecast. The forecast column has a vallue for every day of the month, the actuals depends on where in the month we are up to. It is possibile that companies have data up to different actual dates.
We group our companies into pods (i.e. 2-4 companies in each pod). What I would like to do is to calulate the POD varaince to forecast, butstruggling with the different date thing.
POD 1 should retun 1.89% and POD 2 -5.11%
any suggestions, I tried Sumx and a normal calculate but have struggled to get ar oound the different dates issues, i.e. it keeps pulling the forecast for the max date of the pod not treating each company as an individual.
thanks for any hlep