Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 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 ...
  • 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 

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 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
4 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)