Way to add months

[dfox09]

I have a table that shows revenue by producer by month which I am trying to build a forecast for. The current formula will not show a forecast for any future months as there is no productivity that has taken place yet. How do I prepopulate future months by producer?

Here is the formula that I am currently using based on help from Ibendlin.

Forward Forecast5 =

var m = SELECTEDVALUE(DimDate[StartOfMonthDate])

var t = EDATE(m,-12)

var i = DATEDIFF(t,Date(2020,04,30),MONTH)

var a = CALCULATE(SUM(TBL_Summarized_Actuals[Revenue]),

DATESBETWEEN('DimDate'[Date],t,DATE(2020,04,30)))

Return i

[amitchandak]

@dfox09 ,Can you share sample data and sample output in table format?

[dfox09]

Here is sample data for one producer. You will see thatthere is no revenue past 202006 (calendar month).

Here is a table with revenue for June and July but nothing after. Where it says "Infinity" I would like that populated based on the formula heretofor mentioned.

Don

[dax]

Hi @dfox09 , 

I am not sure whether you want to get result like below

You could refer to my sample for details(create a calendar table, then use this in Table and set "Show items with no data")

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dfox09]

HI Zoe,

Thank you for the suggestion. I am already using that and I have the dates populated but I think the formula is getting hung up on the months with no data. The other thing, is that the formula, itself is not calculating correctly when I compare it to Excel.

Excel AVG is the correct calculation.