cancel
Showing results for 
Search instead for 
Did you mean: 

Combine Sales and Forecast

New Contributor
647 Views
Highlighted
natelpeterson New Contributor
New Contributor

Combine Sales and Forecast

Intro:

There are many ways to analyze Actual Sales and Forecasts. One commonly used metric is the Year-End forecast, which combines Actual Sales with Forecasts for future dates. 

 

This example uses monthly values for both Sales and Forecast. Daily data will work with these measures also. 

 

Date Cutoff:

A future date is any date which does not have Sales information available. There are two ways to mark the cutoff date:

1. Create a new column in the date table, which indicates whether or not each date is before or after the cutoff date. In this case, I use an M script for the date dimension. Since the Sales data is at the month level, it finds the most recent month with Sales and then checks whether the month is before or after that month:

 

maxSales = List.Max(Table.Column(Sales, "yearmonth")),
InsertHasSalesData = Table.AddColumn(#"Changed Type", "Has Sales Data", 
each if [Month Key] <= maxSales then "Yes"
else "No")

2. Create a new Measure. In this case, we already have the dates marked, so we can use that information:

 

 

Max Sales Date = 
CALCULATE(
    MAX('Date'[Date]),
    FILTER(
        ALL('Date'),
        'Date'[Has Sales Data] = "Yes"
    )
)

Alternatively, the Sales date could be referenced. (Again, this example uses monthly data - this will not work here):

 

 

Max Sales Date = 
CALCULATE(
    MAX('Sales'[Sales Date]),
    ALL('Date')
)

 

 

Connecting Actuals and Forecast in Line Charts:

Using the cutoff date, the Forecast can be limited to only consider later dates. However, to make a continuous line chart, the measure also needs to match the Actuals on the max Actuals date. Monthly and YTD measures can be accomplished as follows:

 

Amount Forecast Future Only With Final Sales = 
var curdate = MAX('Date'[Date])
return SWITCH(
    TRUE(),
    curdate < [Max Sales Date], BLANK(),
    curdate = [Max Sales Date], [Amount Sales],
    [Amount Forecast Future Only]
)
Amount Forecast Future Only YTD With Final Sales = 
var curdate = MAX('Date'[Date])
return SWITCH(
    TRUE(),
    curdate < [Max Sales Date], BLANK(),
    [Amount Sales YTD] + [Amount Forecast YTD Future Only]
)

Cheers!

 

Nathan