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 BI

Leave a Comment