DAX: Increasing Forecast by a certain percentage in different months

[dattron]

Hello, first time posting here!

I have been experimenting on how to increase current future forecast by a certain percantage. I have managed to do this in Power BI but only across all the months and by one % value. I did this by adding a slicer visualisation then created a measure that will take the base forecast multiple by the percentage which the user put using the slicer. 

I am now wondering whether it would be possible increase the forecast by different percentages for certain months. Please see below example in Excel that I want to replicate in Power BI.

Thanks

[v-jayw-msft]

Hi @dattron ,

If your original data is the same as it shown above, you will need to use unpivot feature to transform it as below:

Selecte [unit sold] and click "unpivot other columns".

Then create measures and create visual like below:

Measure = CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[units sold]="Base"))*(CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[units sold]="upleft"))+1)

Measure 2 = CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[units sold]="Base"))

Best Regards,

Jay

[sturlaws · Accepted solution]

Hi, @dattron, 

there are several approaches to this, depending on your scenario. If you are the only user, you can create an excel-file with the modifications for each month, and create a relationship or lookup to get the modifications included in the measure. Or you can replace excel with "enter data" in power query and create the modifications there.

If you have report users who need to able to change the modifications, you can create a standalone table with values e.g. -50:50 for each month. Then create a slicer for each of these tables and write your measure like this:

Measure = 
var _s1 = (100 + CALCULATE(max('slicer month 1'[Value])))/100 return
var _s2 = (100 + CALCULATE(max('slicer month 2'[Value])))/100 return
switch(
    true(),
    CALCULATE(SELECTEDVALUE('Table'[month]))=1,SUM('Table'[value])*_s1,
    CALCULATE(SELECTEDVALUE('Table'[month]))=2,SUM('Table'[value])*_s2,
    SUM('Table'[value]))

Here is a mockup report I have created to highlight this: mockup 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.