Future dated forecast

[msa_007]

Hi All,

I have a line chart with a reducing value up to today's date and I would like to forecast the data points for future dates based upon a rate of decrease (e.g. 5%), until the value reaches zero.

I am a little in the dark on how to do this so any advice would be useful..  I have tried various things but none of those attempts have worked properly.  I would prefer not to use the in-built forecast feature of a line chart.

Rate of Decrease = 
    var FirstValue = 
        MAXX(
            VALUES('Date'[Date]),
            [Handover Countdown]
        )
    var LastValue = 
        MINX(
            VALUES('Date'[Date]),
            [Handover Countdown]
        )    
RETURN
( FirstValue - LastValue ) / (1- LastValue)

Many thanks.

[Anonymous · Accepted solution]

Hi, @msa_007 

You can try this measure to solve your need.

Rate of Decrease = 
VAR _FirstValue =
    CALCULATE ( MAXX ( VALUES ( 'Date'[Date] ), [Measure] ), ALL ( 'Table' ) )
VAR _LastValue =
    CALCULATE ( MINX ( VALUES ( 'Date'[Date] ), [Measure] ), ALL ( 'Table' ) )
VAR _dayDiff =
    CALCULATE ( DATEDIFF ( MIN ( 'Date'[Date] ), TODAY (), DAY ), ALL ( 'Table' ) )
VAR _valueDiffBefore = _LastValue - _FirstValue
VAR _decrementalValue =
    DIVIDE ( _valueDiffBefore, _dayDiff )
VAR _daysFeature =
    DIVIDE ( _LastValue, - _decrementalValue )
VAR _endDate =
    TODAY () + _daysFeature
VAR _dayDiffAfter =
    DATEDIFF ( TODAY (), SELECTEDVALUE ( 'Date'[Date] ), DAY )
VAR _dayAddDecrementalValue =
    IF (
        _dayDiffAfter > 0
            && _dayDiffAfter <= _endDate,
        _LastValue + _dayDiffAfter * _decrementalValue
    )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Date'[Date] ) <= TODAY (), SELECTEDVALUE ( 'Table'[Value] ),
        _dayAddDecrementalValue
    )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum