Forecasting future reductions using Dax

[Anonymous]

Hello! Good day!

I'm trying to forecast an annual 12% reduction using DAX. I tried to implement it but the reduction does not work and the same value is being repeated. I would appreciate if anyone can point me in the right direction.

My table strcuture is as follows.

 

Here is my Forecast query. I'm trying to stop the Year column from continuing when the value is less than 1 as well. 

Total = SUM('Measure'[Revenue])

Forecast = 

VAR reductionPercentage = 12/100
VAR forecast = CALCULATE ( [Total]-[Total]*reductionPercentage, 'Year'[Year] < MAX('Year'[Year] ))

RETURN
    IF (
        ISBLANK([Total]),
        IF(forecast < 1,
            BLANK(),
            forecast
        ),
        BLANK()
    )

And the incorrect result.

I tried to do the same thing is Excel and it worked without any issues. 

I have spent a lot of time trying to figure this out but unfortunately, I couldn't.

Can someone please help me?

Thank you in advance. 

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

The relationship between these tables makes no sense. Please delete it. Please try this Forecast measure as below.

Forecast =
VAR _reductionPercentage = 12 / 100
VAR _forecast =
    CALCULATE (
        [Total]
            * POWER (
                1 - _reductionPercentage,
                MAX ( 'Year'[Year] ) - MIN ( 'Measure'[Year] )
            )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Year'[Year] ) >= MAX ( 'Measure'[Year] ),
        FORMAT ( _forecast, "#.000" )
    )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you. This is exactly what I was looking for. I did a small modification to return the values only when greater than 1 to avoid all the unnecessary zeros like this

IF(SELECTEDVALUE('Year'[Year])>=MAX('company'[Year]),
   IF(_forecast > 1, _forecast, BLANK()), BLANK())