Forum Discussion

Chedva's avatar
Chedva
Helper II
6 years ago
Solved

LE forecast

I want to create a LE forecast calculation.   I have an Actual table and a Forecast table.   The Actual Table has the month of the actual, and QTY and it is with a relatioship to a table dates.  ...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Chedva 

    If you actual and forecast is joined with date table.

    Ideally, you should create a month start date in the forecast table and join with the same date table. If now do so. I am not sure of Month format, I can suggest one

    You can measure like

    QTY actuals = Sum(actuals[QTY])

    QTY forecast= Sum(forecast[QTY])

     

    if(eomonth(Today(),-1)<=max(Date[Date]), Sum(actuals[QTY]) , Sum(forecast[QTY]) )

  • Chedva's avatar
    Chedva
    6 years ago

    This is how i solved it 
     

    LE Changing =
    var _SF = EOMONTH(max('Dates Slicer'[DateKey]),-1)
    Return
    if(_SF < max(Dates[DateKey]), 'Forecast Consol'[Current Month Forecast], 'Actual Consol'[Contract Actuals])
     
    LE Current =
    var _LASTMONTH= EOMONTH(today(),-1)
    Return
    if(_LASTMONTH < max(Dates[DateKey]), 'Forecast Consol'[Current Month Forecast], 'Actual Consol'[Contract Actuals])
amitchandak's avatar
amitchandak
Super User
6 years ago

Chedva 

If you actual and forecast is joined with date table.

Ideally, you should create a month start date in the forecast table and join with the same date table. If now do so. I am not sure of Month format, I can suggest one

You can measure like

QTY actuals = Sum(actuals[QTY])

QTY forecast= Sum(forecast[QTY])

 

if(eomonth(Today(),-1)<=max(Date[Date]), Sum(actuals[QTY]) , Sum(forecast[QTY]) )

Chedva's avatar
Chedva
Helper II
6 years ago

amitchandak I've been using this solution for a while - but I noticed

If I diplay it by month it give me the right results, but once I display By QTR it shows me for the current QTR only the forecast for June instead of Actuals April & May +Forecast June.

If I diaplay for this year, it shows only the forecast till the end of the year from this month. 

so you know how to fix thw agrergation to pick up both actuals and forecast?

Thank!