How to calculate forecast based on previous 2 months with multiple filters

[chuckbo]

I'm trying to re-learn my Power BI. I know I did this two or three years ago, but I'm not at that company any more and I don't remember how I did it.

I have a table of Costs. The keys are Location, Fiscal month/year, and Vendor. There's also an Order# field because we can have multiple payments to the vendor each month. I have a chart that plots the running total for the Cost, and there are slicers for location, fiscal year, and vendor. (I used the quick measure to make a running total for the cost.)

I've been asked to create a graph that shows actual values for the past months and forecasts future costs where the future month = the average of the preceding two months. Thus July forecast = (May actual + June actual)/2. But August forecast = (June Actual + July Forecast)/2. And September forecast = (July Forecast + Aug Forecast) / 2.

I have dimension tables for Date, Vendor, and Location.

The complexities that I'm dealing with:

- forecast shifts from using actuals to forecasts;

- I have to sum the Orders for a month before I average the two totals); and,

- this has to work with the slicers for Location, Fiscal year, and vendor.

[tamerj1]

Hi @chuckbo 

that is pretty complicated. You may follow this 

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Recursive-Calculation-and-Forecast-m...

[Sahir_Maharaj]

Hello @chuckbo,

1. Create a measure for Actual Costs:

Actual Cost = 
CALCULATE(
    [Running Total Cost],
    NOT(ISBLANK([Running Total Cost]))
)

2. Create a measure for Forecast:

Forecast = 
VAR SelectedDate = MAX('Date'[Date])
VAR PreviousMonth = CALCULATE(MIN('Date'[Date]), 'Date'[Date] < SelectedDate, 2)
VAR PreviousTwoMonthsActuals = CALCULATE([Actual Cost], 'Date'[Date] >= PreviousMonth && 'Date'[Date] < SelectedDate)
RETURN
IF(
    ISBLANK(PreviousMonth) || ISBLANK(PreviousTwoMonthsActuals),
    BLANK(),
    DIVIDE(
        [Actual Cost] + CALCULATE([Actual Cost], 'Date'[Date] = PreviousMonth),
        2
    )
)

3. Create a measure for Forecast Shift:

Forecast Shift = 
IF(
    MAX('Date'[Date]) > MAX('Date'[Date]),
    [Forecast],
    [Actual Cost]
)

4. Create a line chart and use the "Forecast Shift" measure as the value. Add the "Date" to the axis, and use slicers for "Location," "Fiscal year," and "Vendor" to filter the data.

Hope this helps!

[chuckbo]

Sahir,

I'm trying both of the solutions that were sent to me. 
When I enter your formula for Actual Cost, I get an error:
A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[chuckbo]

Sahir,

thank you for the reply. I can try it out on Monday.

something I don't think I see, though... I see where I'm taking the average of the actual cost and the previous month's cost. But will this still work when I'm two months removed from an actual cost and have to rely on the forecast for the previous two months? The problem I couldn't overcome was that I could t get past the recursive nature of the formula.