forecast line for next 12 months based on last budget figure

[Anonymous]

Hi All, 

I have budget data until dec-21 but i wish to have a line that continues 2022 using dec 21 data. i.e. have the budget line drag on for the next 12 months. Is there a measure that i can use to accomplish this? 

I am currently also using the stacked and line column chart and therefore cannot utilise the analytics pane. 

[HarishKM · Accepted solution]

@Anonymous Hey,
You can use below dax formula to fulfil your requirement.

You have to crearte a measure then paste below measure to get the same result.

Sample test measure =

VAR LastNonBlankDate =

CALCULATE (

MAX ( Sheet1[Date] ),

FILTER (

ALL ( Sheet1 ),

Sheet1[Date] <= MAX ( Sheet1[Date] )

&& Sheet1[Budget] <> 0

)

)

RETURN

CALCULATE (

SUM ( Sheet1[Budget] ),

FILTER ( ALL ( Sheet1 ), Sheet1[Date] = LastNonBlankDate )

)

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.

[Anonymous]

Thank you soooooo much!! 

Just one qn, Do i have to create new extra dates for 1/1/2022 onwards so that the line will drag on? 

[HarishKM]

@Anonymous Hey ,
I believe you are using a calendar table as well then from that only it will fulfill your requirement . it will have future date as well .if not then create a new date .

[selimovd]

Hey @Anonymous ,

sure, you can check if [Current avail data] is filled. If yes, then give back the measure, otherwise give back the last existing value.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[Anonymous]

Thanks alot for the quick reply. But how would you write is filled in the formula? and also last existing value?