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
eyJrIjoiZDJmNTQwZDctODI3ZS00ODg5LWFiYzctODk4NzYzYzIxMTcyIiwidCI6IjM1MDY5ZDc0LTE0ODktNDE5NC04MGM3LTNhODEzODVlYWQ1YiIsImMiOjh9
