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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Helm
Regular Visitor

Combining multiple running totals

Hey

I'm currently in a situation where I'm trying to visualize our budget and our current expenses. This is done through a running total which haven't really given me any issues. 
However the budget has data for all months throughout the year, while the current expenses are on a YTD and thus I only have values up until today.
This gives me a graph where one line goes through the entire year, and one stops in the middle of the year. For the one that stops in the middle of the year I would like to make a "forecasting" line that simply follows the exact same trend as the budget, but is shifted with the difference between the budget and the current expenses at the current date. I have tried to show this with a blue line below, where everything would be shifted by 0,02 mio. as calculated by the grey line.

Helm_0-1659517479690.png

The running totals has been calculated using the following formulas:

CALCULATE(
	[BUDGET/EXPENSES], -- This is simply a sum(data[data]) measure
	FILTER(
		CALCULATETABLE(
			SUMMARIZE('Date', 'Date'[Month #], 'Dato'[MonthName]),
			ALLSELECTED('Date')
		),
		ISONORAFTER(
			'Date'[Month #], MAX('Date'[Month #]), DESC,
			'Date'[MonthName], MAX('Date'[MonthName]), DESC
		)
	)
)

My initial thought was simply doing another running total that would be calculated as the other ones but with the condition added to the dataset 

CALCULATE(
	IF(SELECTEDVALUE('Date'[Month #]) > MAX(Expenses[Date]),EXPENSES,BUDGET), 
	FILTER(
		CALCULATETABLE(
			SUMMARIZE('Date', 'Date'[Month #], 'Dato'[MonthName]),
			ALLSELECTED('Date')
		),
		ISONORAFTER(
			'Date'[Month #], MAX('Date'[Month #]), DESC,
			'Date'[MonthName], MAX('Date'[MonthName]), DESC
		)
	)
)

But this simply seems to give me the budget for the entire duration instead of a combination of the two. Other solutions I have tried is getting the difference at the YTD point and adding it to the Expense running total, but that doesn't seem to work as all of my measures for calculating the difference between Budget and Expenses seems to also be context specific and can't simply be saved as a static value, even then using the following 

CALCULATE(BUDGET-EXPENSES, SELECTEDVALUES('Date'[Month]) = MAX(Expenses[Date]))


Any ideas or help would be appreciated.

I am sadly using a DirectQuery to a dataset that I currently cannot update, meaning that I won't be able to do the calculations directly in the dataset.


Thanks in advance!

1 ACCEPTED SOLUTION
daXtreme
Solution Sage
Solution Sage
1 REPLY 1
daXtreme
Solution Sage
Solution Sage

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.