Rolling estimate Act/Bud in Power BI

[Anonymous]

Hi.

I have my own ideas, but I don't find any of them to be optimal, especially performance wise. So I'd like to ask here, how you would go about this:

Data model:

To make matters simple, I have a Calendar table related 1:* to my FinanceActual and FinanceBudget fact tables. I also have a roleplaying Calendar table, CalendarRole, which is related (inactive) 1:1 to my Calendar table. I have acreated a simple measure on each of the fact tables:

- ActSum = SUM ( FinanceActual[Amount] )

- BudSum = SUM ( FinanceBudget[Amount] )

Report page:

On my Report page, I have a page level filter on Fiscal Year = 2022/2023 and FiscalMonth = 5 ( November ) from the Calendar table

I now want to create a matrix visual that always show all 12 months of the filtered FiscalYear as separate columns. However, the figures must change based on the selected month, so that it shows actual in past and current month, and budget in future months ( so in the above example it would need to display actuals in columns Jul - Nov and budget in columns Dec - Jun.

The solution must be as simple and performance effective as possible, and preferably if possible, not make use of the CalendarRole table. 

Bonus Question: The Column Total should then be the Estimate combined sum of the 12 months, but in addition to this I'd like one more total column with only the budget total for the fiscal year, without showing budget amount as a separate columns for each month.

Thanks alot in advance 🙂

[amitchandak]

@Anonymous , if you want to select a month and then you want 12 month on trend (axis) then you need use an independent date for the slicer.

example

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

if you just want 12 month sum/avg etc. you can try like

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))