topic Re: Dynamic latest load variable for Full Year forecast in DAX Commands and Tips

Dynamic latest load variable for Full Year forecast

Anonymous — Mon, 19 Sep 2022 16:21:34 GMT

I have a table I'm showing which has several lines, all of which are measures including Actual Sales YTD and Forecast Sales Full Year.

The data is loaded right after the end of the previous quarter (Q1 data is loaded on April 1st)

The calculation for Actual Sales YTD is quite simple (Sum of all sales for this year until previous quarter).

The calculation for Forecast is Actuals up to previous quarter than Forecast for the end of the year.

Here is a simplified version of that data

Index	Type	Effective Quarter	Amount	LoadDate
1	Actuals	Q1	75	Apr-1-2022
2	Forecast	Q2	60	Apr-1-2022
3	Forecast	Q3	65	Apr-1-2022
4	Forecast	Q4	70	Apr-1-2022
5	Actuals	Q2	63	Jul-1-2022
6	Forecast	Q3	68	Jul-1-2022
7	Forecast	Q4	72	Jul-1-2022

So at the end of Q1

Actual Sales YTD: 75

Forecast Full Year: (75+60+65+70) 270

And at the end of Q2

Actual Sales: 138 (75+63)

Forecast Full Year: 278

Since we have several forecasts during the year I can't just add up the rows called forecast so I have a Latest_Forecast variable which helps me dtermine to only add the right forecast

VAR Latest_Forecast =

MAXX (

FILTER (

'Sales Data',

'Sales Data'[Type] = "Forecast"

'Sales Data'[LoadDate]

)

My problem (finally I get to it), is that the user now wants to be able to go back to previous quarters and still see the right Forecast for year end.

So right now if I select Q1 it will give me 215 (75 + 68+72) because as far as the latest_forecast is concerned thats the Latest forecast (and also it doesnt contain any Q2 values)

How can I change the variable to group together the rows per quarter to determine what the latest forecast is per quarter?
It also doesnt help that the table i put above is heavily simplified when it reality there are thousands of rows that make up the actuals and forecast per quarter.

Any help would be much appreciated and please let me know if I can clarify further, it's a bit of a doozy to explain and I'm afraid I might now have done a great job of laying it out.

Re: Dynamic latest load variable for Full Year forecast

amitchandak — Mon, 19 Sep 2022 15:43:55 GMT

Anonymous , With help from a date table, try measure like

Actual +forecast =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
var _YED = eomonth(_max,12-1*MONTH(_max))
return
CALCULATE(Sum(Table[Amount]), filter(Table, [Type] = "Actual") ,DATESBETWEEN('Date'[Date],_min,_max)) +
CALCULATE(Sum(Table[Amount]), filter(Table, [Type] = "Forecast") ,DATESBETWEEN('Date'[Date],_max+1,_YED))

Re: Dynamic latest load variable for Full Year forecast

Anonymous — Mon, 19 Sep 2022 16:14:34 GMT

Maybe I'm missing something but I feel like that measure filters on the right dates but doesn't take in consideration the multiple forecast loads. How does it know which load date to use?

If I select to view data from Q1 it should use the Apr-1 loaddate but if I select Q2 it should be using the Jul-1 loaddate (and so on and so forth)

Re: Dynamic latest load variable for Full Year forecast

Anonymous — Mon, 19 Sep 2022 18:28:53 GMT

Figured it out! changed the latest_forecast variable to match the load date quarter -1 selected value on the slicer and added the extra filter within the CALCULATE