topic Re: Forecast percentage from measure in DAX Commands and Tips

Forecast percentage from measure

Solle — Wed, 13 Sep 2023 15:45:05 GMT

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,

Re: Forecast percentage from measure

tamerj1 — Thu, 14 Sep 2023 04:54:46 GMT

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 )

Re: Forecast percentage from measure

Solle — Thu, 14 Sep 2023 10:22:25 GMT

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,