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