Forum Discussion
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.
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
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]) )
This is how i solved it
LE Changing =var _SF = EOMONTH(max('Dates Slicer'[DateKey]),-1)Returnif(_SF < max(Dates[DateKey]), 'Forecast Consol'[Current Month Forecast], 'Actual Consol'[Contract Actuals])LE Current =var _LASTMONTH= EOMONTH(today(),-1)Returnif(_LASTMONTH < max(Dates[DateKey]), 'Forecast Consol'[Current Month Forecast], 'Actual Consol'[Contract Actuals])
4 Replies
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]) )
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 !
This is how i solved it
LE Changing =var _SF = EOMONTH(max('Dates Slicer'[DateKey]),-1)Returnif(_SF < max(Dates[DateKey]), 'Forecast Consol'[Current Month Forecast], 'Actual Consol'[Contract Actuals])LE Current =var _LASTMONTH= EOMONTH(today(),-1)Returnif(_LASTMONTH < max(Dates[DateKey]), 'Forecast Consol'[Current Month Forecast], 'Actual Consol'[Contract Actuals])
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!