Forum Discussion

msa_007's avatar
msa_007
Frequent Visitor
1 year ago
Solved

Future dated forecast

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...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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