Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Dynamic latest load variable for Full Year forecast

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

IndexTypeEffective QuarterAmountLoadDate
1ActualsQ175Apr-1-2022
2ForecastQ260Apr-1-2022
3ForecastQ365Apr-1-2022
4ForecastQ470Apr-1-2022
5ActualsQ263Jul-1-2022
6ForecastQ368Jul-1-2022
7ForecastQ472Jul-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. 
  • Anonymous's avatar
    Anonymous
    3 years ago

    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 

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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))

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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)

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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