Combine Actuals + Forecast in Power BI
You want to show actual and forecasted future values as a connected line or in bar chart but with different conditional formatting?
Before starting it, I would suggest that you have or create a calendar date table and connect in a right way to the Actuals and Forecast tables. How to create a calendar table you will find in another article
At the end of the article will show you the right way of data model relationships.
Forecast Projection for Completed Month
Let say that your data model has two tables: Sales Actuals and Forecast
Suppose you have created two measures: Sales Actuals and Sales Forecast.
Sales Actuals = SUM(Sales[SalesAmount])
Sales Forecast = SUM(Forecast[ForecastAmount])
Then drag and drop them in Y-axis.
The result will be:
In order to put them into one line then you need two measures – one that returns the actual values for example Sales Actuals and the other measure that returns the actual values for dates before the last sales date and forecasted values after like Sales Actuals + Forecast.
The order of the measures is shown below. Sales Actuals is below
The trick here is that they are overlapping.
The first step is to create Sales Actuals + Forecast measure
You need to find that last sales date which you can do with a max function as this returns the last sales date in the filter context you need to remove any filter on date using calculate and remove filters while you might think of using an if function this would return the wrong values if you want to show totals.
I will show 3 versions based on modifying the filter context in forecast variable and one showing wrong total.
The second alternative (v2) is to simply write your filter condition but wrap it in a KEEPFILTERS function so that it doesn’t override the filter from the visual place the measure on a line chart and add the actuals measure below it on values
Sales Actuals + Forecast (Wrong Total) =
VAR LastSalesDate = CALCULATE(MAX(fctSales[DateKey]), REMOVEFILTERS()) -- 30 June 2020
RETURN
IF(
MAX('Date'[Date]) > LastSalesDate,
[Sales Forecast],
[Sales Actuals] )
For example in Version 1, you can use a CALCULATE function and modify the filter by taking the dates in the filter context with the VALUES function and then filled those to the dates after the last sales date
Sales Actuals + Forecast v1 =
VAR LastSalesDate = CALCULATE(MAX(fctSales[DateKey]), REMOVEFILTERS('Date')) -- 30 June 2020
VAR Actuals = [Sales Actuals]
VAR Forecast = CALCULATE([Sales Forecast],
FILTER(
VALUES('Date'[Date]),
'Date'[Date] > LastSalesDate) )
RETURN
Actuals + Forecast
Sales Actuals + Forecast v2 =
VAR LastSalesDate = CALCULATE(MAX(fctSales[DateKey]), REMOVEFILTERS()) -- 30 June 2020
VAR Actuals = [Sales Actuals]
VAR Forecast = CALCULATE([Sales Forecast],
KEEPFILTERS('Date'[Date] > LastSalesDate))
RETURN
Actuals + Forecast
Sales Actuals + Forecast v3 =
VAR LastSalesDate = CALCULATE(MAX(fctSales[DateKey]), REMOVEFILTERS()) -- 30 June 2020
VAR Actuals = [Sales Actuals]
VAR Forecast = CALCULATE([Sales Forecast],
FILTER(
'Date',
'Date'[Date] > LastSalesDate) )
RETURN
Actuals + Forecast
Here is the variance in filter context
Here is the result with correct Total:
Dynamic Forecast Projection for
Incomplete Current Month Sales
Actual + Forecast (Incomplete Current) =
VAR Previous_Month_Historic =
CALCULATE(EOMONTH(MAX(fctSales[DateKey]),-1),
REMOVEFILTERS('Date')) --latest sales completed date e.g. 31 May 2020 (1 month back)
VAR Previous_Month = CALCULATE(EOMONTH(TODAY(),-1),
REMOVEFILTERS('Date')) --based on current date (TODAY) or May 2022
VAR Actuals =
CALCULATE([Sales Actuals],
KEEPFILTERS('Date'[Date] <= Previous_Month_Historic) )
VAR Forecast =
CALCULATE([Sales Forecast],
FILTER(
VALUES('Date'[Date]),
'Date'[Date] > Previous_Month_Historic))
RETURN
Actuals + Forecast
This is dynamic transition from Actuals to Forecast based on current month. If the current month is not complete (e.g. now is 10th of June) we will show forecast for June
As you may notice, the example in this article I am using historical data (2020) but you data may be updated daily and using functions like TODAY() is what you need.
In that case change the variable from Previous_Month_Historic to Previous_Month
If you prefer to show in bar chart, you may create conditional format measure to change the color from Actuals to Forecast
Sales Actuals + Forecast v1 Color =
VAR LastSalesDate = CALCULATE(MAX(fctSales[DateKey]), REMOVEFILTERS('Date')) -- 30 June 2020
VAR Actuals = [Sales Actuals]
VAR Forecast = CALCULATE([Sales Forecast],
FILTER(
VALUES('Date'[Date]),
'Date'[Date] > LastSalesDate) )
RETURN
IF(Actuals,1,0)
Then use this measure as a Rule (=0 gray) in conditional formatting of the bar chart
The result will look like:
Data Model
Here is the right way of table relationship of the data model
Tags:
Power BIRecent Comments
Archives
Categories
Search
By transforming and visualizing our data, the dashboards that "Allure Analytics" created helped us in tracking and evaluating our employees' activity at all sales hierarchy levels – from the sales representatives to the national sales managers. Now we can easily see all of the key performance indicators of our employees, such as the number of calls made in a certain period, the number of customers visited, and the number of customers in the different categories. This helps us in keeping track of the achievements.
Julien B.
Associate Director, Pharmaceutical CompanyBy transforming and visualizing our data, the dashboards that "Allure Analytics" created helped us in tracking and evaluating our employees' activity at all sales hierarchy levels – from the sales representatives to the national sales managers. Now we can easily see all of the key performance indicators of our employees, such as the number of calls made in a certain period, the number of customers visited, and the number of customers in the different categories. This helps us in keeping track of the achievements.
Julien B.
Associate Director, Pharmaceutical CompanyBy transforming and visualizing our data, the dashboards that "Allure Analytics" created helped us in tracking and evaluating our employees' activity at all sales hierarchy levels – from the sales representatives to the national sales managers. Now we can easily see all of the key performance indicators of our employees, such as the number of calls made in a certain period, the number of customers visited, and the number of customers in the different categories. This helps us in keeping track of the achievements.
Julien B.
Associate Director, Pharmaceutical CompanyCategories
- Power BI (7)