Hi all,
I have a simple Forecasting-file where we register forecasts as below:
Customer Number | SKU | Units | Forecast Shipping Date | Status |
This is linked in PowerPivot (though relationships with the date and customer dimension tables) to a pricing table showing the various prices after discounts for different customers, Products (SKUs) and currently Year:
Customer Number | SKU | Price per unit | Year |
The formula I use to calculate revenue is pasted at the bottom.
Current scenario is that for some customers, the prices are not static for one particular year, and are valid from say, 1st April through 31st March the next year. Consequently, the revenue calculated for Q1 will be incorrect.
My initial thought was to replace the Year column with a Start and End dates, but I am not sure how I would incorporate this into the measure.
How would you set this up?
Measure:
Revenue =
VAR Revenue = SUMX (
f_Forecast,
f_Forecast[Units]
* CALCULATE (
MAX ('fAccount Overview'[Price per unit] ),
FILTER (
ALL (
'dAccount Overview'[SKU],
'dAccount Overview'[Customer Number],
'dAccount Overview'[Year]
),
'dAccount Overview'[SKU] = f_Forecast[SKU]
&& 'dAccount Overview'[Customer Number] = f_Forecast[Customer Number]
&& 'dAccount Overview'[Year] = YEAR (f_Forecast[Forecast Shipping Date] )
)
)
)
Return
IF(Revenue=BLANK(),0,Revenue)
