LE forecast

[Chedva]

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. 

The forecast table has Calculated_month - the month that the forecast was done related to a table Date slicer, and a Month - the month of the forecast that is related to the table dates (same field as the Actual)

I want to create a measure that will do te folllowing:

If( eomonth(Calculated_month,-1)> Month, QTY actuals, QTY forecast)

The problem is that in the actual table I don't have a column with calculated month

How can I solve this in Power BI?

(I have a table in the report that displays Calculated_month, month, actual QTY, forecast QTY, doing it on excel is super easy once I export)

Hope I was clear, thanks in advance

[amitchandak · Accepted solution]

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

[amitchandak · Accepted solution]

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

@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!

[Chedva]

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

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 · Accepted solution]

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