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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Sumsar
Helper II
Helper II

Rolling estimate Act/Bud in Power BI

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 🙂

1 REPLY 1
amitchandak
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.

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.