Calculating last 12 months average with mix of actuals and budget

[nl_user]

Hi all, 

I am currently working on calculation of last 12 month for specific type of expense. I have two important columns: actuals and budget. Last 12 months for actuals was easy to calculate, but I am facing issues when trying to write the measure for budget, since I would like to have non-standard view for this last 12 month average. This is my table and data that I have:

For last 12 months of budget expense I want to set up a measure that will take into account budget for YTD period and actuals for months related to last year. Example: September 2022 last 12 month = (September budget YTD + Oct '21 actual + Nov '21 actual + Dec '21 actual). Same would be valid for next month: October 2022 last 12 month = (October budget YTD + Nov '21 actual + Dec '21 actual). 

I would really appreciate your help with this question. 

Regards

cevu

[lbendlin · Accepted solution]

so you want the sum of budget for the current year plus the actuals for the prior year if the 12 month window "leaks"  into the prior year? That would mean for December it would be the sum of budget only?

L12M = 
var m = month(max(Date_dim[Date]))
var y = year(max(Date_dim[Date]))
var b = CALCULATE(sum(Table1[Budget]),month(Date_dim[Date])<=m,year(Date_dim[Date])=y)
var c = CALCULATE(sum(Table1[Actual]),month(Date_dim[Date])>m,year(Date_dim[Date])=y-1)
return b+c

[nl_user]

That is correct Ibendlin - thank you very much for your help and solution!

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[nl_user]

Thank you for sugesstion Ibendlin. 

In this message I am attaching link with the example - PBI file: https://drive.google.com/file/d/1f_Yveq78Vt6MQoF5dzL9wB4OwcZ1T8hj/view?usp=sharing. 

As it can be seen from the sample, meassure which I used for last 12 month actual sales is: 

My issue is meassure which I would like to create for budget. Namely, I need budget last 12 month figure for 2022 which will be calculated as: 

September budget last 12 months = 

  Oct '21 Actual

+Nov '21 Actual

+Dec '21 Actual

+Jan '22 Budget

+Feb '22 Budget

+Mar '22 Budget

+Apr '22 Budget

+May '22 Budget

+Jun '22 Budget

+Jul '22 Budget

+Aug '22 Budget

+Sep '22 Budget

Same logic should apply to Oct, etc. I would appreciate if you could let me know if my question is not explained in proper way and of course would as well appreciate your help on this problem. 

Thank you!