Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
Here's how Alberto Ferrari does it: Showing actuals and forecasts in the same chart with Power BI - YouTube
Here's how Alberto Ferrari does it: Showing actuals and forecasts in the same chart with Power BI - YouTube
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
7 | |
6 |