Forecast measure year on year increase

DAX DAX Calculations
1

I have a forecast based on contract end dates that forecasts out using a compound price increase on each anniversary. The Y/N relates to whether a price increase should be applied. All years use the same %age increase per year compounded based on a PriceToThePower column calulated in Query Editor (there may be an easier way to do this)

image

I’d like to make the model more flexible and have the %age increase variable for the first 3 years then using Yr 3 %age for Yrs 4 & 5 so in this case ( It may be easier to have complete flexibility and have parameters for all 5 years?)

image
image902×525 29.2 KB

2021-11 to 2022-10 would be 367 * 1.05 = £385
2022-11 to 2023-10 would be 385 * 1.025 = 395
2023-11 to 2024-10 would be 395 * 1.02 less 395 * 0.5% attrition
2024-11 to 2025-10 would be 401 * 1.02 less 401 * 0.5% attrition
2025-11 to 2025-10 would be 407 * 1.02 less 407 * 0.5% attrition

How can I achieve this?

pbix attached
Forecasting Test - Upload - Copy.pbix (205.9 KB)

2

@AliB ,

A SWITCH (TRUE ()) construct should work perfectly here. Please check out the post below from Expert @Harsh where he shows how to implement this with a compound condition in the first parameter of the SWITCH(TRUE()) using “&&”. That’s the way the cool kids do it, which is equivalent to the more formal construct of using AND(condition 1, condition 2), which in your case will be greater than or equal to the first date and less than or equal to the second date.

I hope this is helpful.

  • Brian
3

Thanks. Here’s what I came up with - it seems to work with lots of variables …

Is there a better way to achieve this?

Total Forecast growth = 

VAR _Forecast = SELECTEDVALUE( ContractDataTest[Forecast?])
VAR _Pricing = SELECTEDVALUE( 'Pricing Scenario'[Pricing Scenario] )
VAR _Attrition = SELECTEDVALUE( 'Attrition Scenario'[Attrition Scenario] )
VAR _PricingYr2 = SELECTEDVALUE('Price Inc Year 2'[Price Inc Year 2])
VAR _AttritionYr2 = SELECTEDVALUE('Attrition Year 2'[Attrition Year 2])
VAR _AttritionYr3 = SELECTEDVALUE('Attrition Yr 3 to 5'[Attrition Yr 3 to 5])
VAR _PricingYr3 = SELECTEDVALUE('Price Inc Year 3'[Price Inc Year 3] )
VAR _PricingYr4 = SELECTEDVALUE('Price Inc Year 4'[Price Inc Year 4] )
VAR _PricingYr5 = SELECTEDVALUE('Price Inc Year 5'[Price Inc Year 5] )
VAR _ForecastRev = [Total Sales] * (1+(_Pricing+_Attrition))
VAR _ForecastRevYr2 = _ForecastRev * (1+(_PricingYr2+_AttritionYr2))
VAR _ForecastRevYr3 = _ForecastRevYr2 *(1+_PricingYr3 + _AttritionYr3)
VAR _ForecastRevYr4 = _ForecastRevYr3 * (1+_PricingYr4)
VAR _ForecastRevYr5 = _ForecastRevYr4 * (1+_PricingYr5)
VAR _NonForecast = [Total Sales]
VAR _Power = SELECTEDVALUE( ContractDataTest[PriceToThePower])
VAR _Year = SELECTEDVALUE(ContractDataTest[PriceToThePower])
RETURN
    SWITCH(TRUE(),
        _Forecast = "N", [Total Sales],
     _Year = 1,_ForecastRev,
    _Year = 2,_ForecastRevYr2  ,
    _Year = 3, _ForecastRevYr3,
    _Year = 4, _ForecastRevYr4,
    _Year = 5, _ForecastRevYr5,
    [Total Sales]
)
4

Bumping this post for more visibility.

5

Hi @AliB, we noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!