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

    Hi,

    Yes my actuals and my forecast tables have a relatioship with a date table.

     

    SF month
    (source - Date slicer table - related to Forecast on SF month column)

    Month

    (source - Date table - related to Forecast on month column and to Actual on Month)

    Actual QTY

    Measure 

    Forecast cons QTY -
    Measure = CALCULATE(sum('Forecast Consol'[Forecast QTY]),USERELATIONSHIP('Forecast Consol'[SF Month],'Dates Slicer'[DateKey])))

    LE - the measure I need

    March 2020

    Jan 2020

    1

     

    1

    March 2020

    Feb 2020

    2

     

    2

    March 2020

    March 2020

    3

    		4

    4

    March 2020

    Apr 2020

    2

    4

    4

    March 2020

    May 2020

     

    7

    7

    March 2020

    Jun 2020

    		 99

     

    The measure I need to do in Power BI is to get the LE column I added - 

    if(EOMONTH(SF month, -1)> Month, Actual QTY, Forecast QTY)

     

    It needs to be dynamic..

     

    Thanks amitchandak !

     

    • Chedva's avatar
      Chedva
      Helper II
      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])
  • 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!