ALLURE ANALYTICS
  • Home
  • About Us
  • Our Services
  • Blog
  • Our Team
  • Contact Us

June 11, 2022

  • By  Allurean
  • 0 comments

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

This scenario is when the transition from Actuals to Forecast is direct and in this example June amount is complete.

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 the version based on modifying the filter context in forecast variable and one showing wrong total.

The second alternative 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('Date'[Date])) -- 30 June 2020
RETURN
IF(
MAX('Date'[Date]) > LastSalesDate,
[Sales Forecast],
[Sales Actuals] )
Sales Actuals + Forecast = 
VAR LastSalesDate = CALCULATE(MAX(fctSales[DateKey]), ALL(fctSales) ) -- 30 June 2020
VAR Actuals = [Sales Actuals]
VAR Forecast = CALCULATE([Sales Forecast],
KEEPFILTERS('Date'[Date] > LastSalesDate))
RETURN
Actuals + Forecast

Here is the result with Correct Total:

Important Note!

Based on great explanation by sqlbi.com - Alberto Ferrari We had to use ALL on Sales when computing the LastSalesDate variable to retrieve the last ever date with sales. Without the ALL modifier, the variable would compute the last date with sales in the filtered period (or context). This would return incorrect figures. The other note is about the use of KEEPFILTERS when filtering the date in the Result variable. Because the filter operates on the DateKey column, which is the primary key of a table marked as a date table, it would override any previously existing filter. Therefore, KEEPFILTERS is needed in order to force the calculation within the currently selected time period.

Dynamic Forecast Projection for
Incomplete Current Month Sales

This scenario is better when compare to last year for example because will not include the current month amount. Let's say we are now 10th of June and the monthly amount is 1/3 of the regular monthly amount. In case we include this month to comparison with last year this will not be correct since we will compare 30% of current month to 100% of last year June amount.
You need to modify the measures in this article based on your data model: table/column names and Actual and Forecast measures
Actual + Forecast (Incomplete Current) =

VAR Previous_Month_Sales = CALCULATE(EOMONTH(MAX(fctSales[DateKey]),-1), ALL(fctSales) )
//based on latest sales completed date e.g. 31 May 2020 (1 month back)
VAR Previous_Month_Current = CALCULATE(EOMONTH(TODAY(),-1), ALL('Date'))
//based on CURRENT DATE (TODAY) or May 2022

VAR Actuals = [Sales Actuals]

VAR Forecast = CALCULATE([Sales Forecast],
KEEPFILTERS('Date'[Date] > Previous_Month_Current ))

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_Sales to Previous_Month_Current

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 Color = 
VAR LastSalesDate = CALCULATE(MAX(fctSales[DateKey]), ALL(fctSales) ) -- 30 June 2020
VAR Actuals = [Sales Actuals]
VAR Forecast = CALCULATE([Sales Forecast], KEEPFILTERS('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 Cancel Reply

Your email address will not be published.*

Recent Posts

  • Data Modeling in Power BI
  • Combine Actuals + Forecast in Power BI
  • Best Practices in Power BI
  • Time Intelligence in Power BI
  • Standard Calendar (Date) table in Power BI

Recent Comments

  1. Mark on DAX basics in Power BI Desktop

Archives

  • October 2022
  • June 2022
  • May 2022
  • August 2021

Categories

  • Power BI

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 Company

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 Company

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 Company

Categories

  • Power BI (7)

Transform Your Data into Actionable Insights with Power BI

Quick Link

  • About Us
  • Contact Us
  • Services
  • Cookies Policy
  • Privacy Policy

Services

  • Work
  • Main Work
  • Blog

Contact Info

  • Sofia, Bulgaria
  • +359(899) 000-000
  • contact@allure-analytics.com

Detox © 2020 All Right Reserved