Forecast value equal to last actual value

[mq2020]

I have a table with amounts per month.

I'd like to create a new column to fill in the future months which are now blank (July 2020 onwards) by using the amount of the previous month (in this case using the amount of June 2020 but this has to change dinamically every month).

I've create the following measure to calculate the amount of the previous month but it will only populate 1 line (July 2020), I dont know how to make it populate all the remaining future months - any ideas?

My current formula is like this:

Prev Month Amount £ = CALCULATE(Sum(Transactions[Amount]),PREVIOUSMONTH('Calendar'[Date]))

[amitchandak]

@mq2020 , only for July

Prev Month Amount £ =
if(eomonth(max('Calendar'[Date]),1) = eomonth(today(),1),
CALCULATE(Sum(Transactions[Amount]),PREVIOUSMONTH('Calendar'[Date])), blank())

for future months try like

Future Month Data =
var _max = eomonth(maxx(Transactions,Transactions[Date]),0)
var _min = eomonth(_max,-1)+1
var _cal = calculate(Sum(Transactions[Amount]), filter(all('Calendar'[Date]), 'Calendar'[Date] >=_min && 'Calendar'[Date] <=_max))
return
if(eomonth(max('Calendar'[Date]),1) >= eomonth(today(),1),_cal,blank())

[mq2020]

@amitchandak , thanks for your help.

I tried both formulas and this is what it returns.

Do I need to modify something in the future months formula so that it populates the rest of months?

Thanks

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[mahoneypat · Accepted solution]

Please try this expression

Prev Month = var __thismonth = SUM(Transaction[Amount])
var __maxdate = CALCULATE(MAX(Transaction[Date]), ALL('Date'))
var __start = DATE(Year(__maxdate), Month(__maxdate),1)
var __prevsales = CALCULATE(SUM(Transaction[Amount]), DATESBETWEEN('Date'[Date], __start, EOMONTH(__start,0)))
return IF(ISBLANK(__thismonth), __prevsales, __thismonth)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat