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 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.

 

 

Need Help
  • 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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    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