Average, Forecast, Target, To Reach Target

DAX DAX Calculations
1

Hi, I have a four measures I would like to create:

  1. 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.

  1. Forecast - based on YTD for current year (2019), and then use Average for remaining months

  2. 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)

  3. 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,
Phil

Average Calcs.pbix (102.9 KB)

2

Hi Phil,

Can we please break these questions out into different topics as per the below

https://forum.enterprisedna.co/t/rules-and-tips-for-asking-support-questions-on-the-forum/30

I’m sure they will be answered much quicker if this is done.

Also based on the example file it’s not clear on how you want to showcase each result, ie what will be the context of the calculation.

Have you made any attempts at these that you can include. This will also assist in working out what formula is ultimately required.

Thanks
Sam

3

Thanks Sam. Have been focused on other projects but will get back to this soon, will split things up and try to be clearer in the outcomes.
Cheers
Phil