Hi, I have a four measures I would like to create:
- Average - based on same month over past “x” years, where “x” is from the Years to Average slicer. Excludes current year.
- have looked at a few of the course tutorials on this but am not really grasping it.
Forecast - based on YTD for current year (2019), and then use Average for remaining months
Target - based on Target table - need to turn annual values into monthly values based on Average. Would also like to understand how to do this at a lower level of granularity, Customer in this case. Note: in the real model, I want to be able to expand the target value to sub-Customer levels that are part of the Customer Hierarchy - just left that out for now)
To Reach Target - Use monthly YTD, then for remaining months, use [Full Year Target] - [YTD] as the amount to achieve in the remaining months, but have them weighted by the % of the Average that month represents compared to all the remaining months Average value.
Have looked through a number of tutorials in the Courses but cannot seem to convert this use case over from those examples.
Thanks in advance,
Average Calcs.pbix (102.9 KB)