Use previous months value as forecast to end of the year

[fazza1991]

Hi,

I would like to get a dax formula to use the previous months' value to be the forecast amount going forward

Month	Revenue	3+9 Forecast
Feb-22	£100	£100
Mar-22	£200	£200
Apr-22	£300	£300
May-22	0	£300
Jun-22	0	£300

[VahidDM · Accepted solution]

Hi @fazza1991 

Try this code to add a new column:

Forecast = 
VAR _A = 'Table'[Month]
VAR _B =
    CALCULATE (
        MAX ( 'Table'[Month] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Revenue] <> 0 )
    )
VAR _C =
    CALCULATE (
        MAX ( 'Table'[Revenue] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Month] = _B )
    )
RETURN
    IF ( 'Table'[Revenue] = 0, _C, 'Table'[Revenue] )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[fazza1991]

I should have mentioned the future dates will not exist in the table for example May & Jun wont be present in the table itself queries itself they only appear as the months go on so i need the dax to add those in assuming the amounts from previous month will be the same

[VahidDM]

@fazza1991 

Add a DATE table to your report and use that for adding future dates.

Check this link:

https://www.vahiddm.com/post/creating-calendar-table-with-3-steps

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[fazza1991]

I have a calendar table, but doesnt seem to add to future dates just stops at the current month

[fazza1991]

Got this to work now - used it as a measure