Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II
Helper II

Rolling estimate Act/Bud in Power BI



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 🙂

Super User
Super User

@Sumsar , 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.



//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
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))


Need of an Independent Date Table:


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))

Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors