Forum Discussion

Helm's avatar
Helm
Regular Visitor
4 years ago
Solved

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.

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!