Forecast percentage from measure

[Solle]

Hi, 
I am trying to create a forecast model, that will have a parameter, which holds the return on equity value, which can be changed dynamically for forecasting purposes. 

The challenge is that I have a measure which is calculating the cumulative return (%) and then for the last value a formula should be applied to calculate a simple forecast based on the latest value. 

The formula used to forecast is [Latest value from measure] * (1+Return on Equity)^t
Please see the example from Excel as per below. 

 

 Does anyone know how to solve this in PowerBI DAX? I have a data table containing both workdays and weekends however days from today should only count workdays. 

Let me know if anything else is required from me to solve this.

Best Regards,

[tamerj1 · Accepted solution]

Hi @Solle 
This is called a semi recursive calculation which is not straight forward when using DAX.

The attached file includes proposed solutions for both "Days From Today" column and the "New Formula" measure. I have no data that would have made it possible to propose a solution for "Cumulative %" Measure

Could not upload sceenshots as currently uploading pictures is not working.

Days From Today = 
VAR CurrentDate = 'Date'[Date]
VAR MaxDate = MAX ( 'Table'[Date] )
VAR Difference = DATEDIFF ( MaxDate, CurrentDate, DAY )
VAR WD = 'Date'[Working Day]
VAR Holidys = CALCULATETABLE ( VALUES ( 'Date'[Date] ), 'Date'[Working Day] = "False", ALL ( 'Date' ) )
VAR NWD = NETWORKDAYS ( MaxDate, CurrentDate, 1,Holidys )
VAR Result = 
    IF ( 
        WD = "True",
        SWITCH (
            Difference / ABS ( Difference ),
            -1, NWD + 1,
            1, NWD - 1,
            0
        )
    )
RETURN
    Result

New Formula = 
VAR RE = 0.15
VAR CurrentDaysFromToday = MAX ( 'Date'[Days From Today] )
VAR IsOneDay = HASONEVALUE ( 'Date'[Date] )
VAR NormalValue = [Cumulative %]
VAR LastValue = CALCULATE ( [Cumulative %], 'Date'[Days From Today] = 0 )
VAR FPeriod = 
    FILTER ( 
        ALL ( 'Date'[Days From Today] ), 
        'Date'[Days From Today] <> BLANK ( )
            && 'Date'[Days From Today] > 0
            && 'Date'[Days From Today] <= CurrentDaysFromToday
    )
VAR Result = PRODUCTX ( FPeriod, ( 1 + RE ) ^ 'Date'[Days From Today] )
RETURN
    IF ( IsOneDay, NormalValue + LastValue * Result )

[Solle]

Hi @tamerj1 

Amazing work! I was struggeling a bit with how to structure the DAX calculation inparticular for the compounding interest part, so thank you for providing an example, I did manage to apply and modify your code to my needs. Appreciate that your took your time to help me out on this issue!

Have a nice day! 

Best Regards,