Show a Variance

[Tony2021]

Hello Experts, 

I have data grouped by month ([Amount] and [Month]. 

I am wanting to show a variance per month between 2 sheets: Forecast and the Actual.

My problem is that one of the data sets (Actual) is a pivot table and is not setup like the other data set (Forecast). 

I am not sure how best to handle this and think an expert would know how to adjust it.  

I am not sure if a pivot table is best to show the variance. 

Please see attached file. 

Grateful for the help 

[#2]

@Tony2021 

I hope I'm interpreting this request correctly (If you're looking to use VAR, the solution can be adjusted).

My solution summarizes the actual data by month (Pivot not used) and then does: Actual - Forecast

'Header - an array constant
={"Date", "Variance"}

'Months - a unique list of months from the actual table. Properly formatted.
=UNIQUE(TEXT(tblActual[Funding Date], "mmm-y"))

'Lambda 'Diff' - it obtains the forecast for a given month by looking up the unique date in the forecast table.  It obtains the actual amount by filtering the actual table by that same unique date.  SUM the results to reduce to 1 value.  Finally,  HSTACK stack the date, and the result of Actual-Forecast.

=LAMBDA(a,v,LET(
    fcast, XLOOKUP(v, tblFC[#Headers], tblFC),
    actual, SUM(FILTER(tblActual[T. Amt of Draw], TEXT(tblActual[Funding Date], "mmm-yy") = v)),
    VSTACK(a, HSTACK(v, actual - fcast))
))

Sheet level formula:
=REDUCE(Header,Months,Diff)

[#3]

Wow. thanks Patrick. That way beyond my skill level. Would you possibly be able to add the sum of the months for the Forecast and the Actual within the variance sheet?

[#4 · Accepted solution]

@Tony2021

Revised workbook attached.

[#5]

@Patrick2788 

Hi Patrick, that is really cool. I failed in my explanation though. Sorry. I meant can the Actual and Forecast numbers be included in the variance output? Basically, what I am looking for is to insert the Forecast amt in Col B and Actual Amt in Col C and Variance in Col D.  I would not need the Total column actually.  I would try to figure it out but it's far beyond my knowledge.  The reason I need the Forecast and Variance numbers in the variance output sheet is because I plan to make a graph.   

Thank you very much and once again apologies on the confusion.  I hope its clear now.  Kindly let me know if not.

[#6]

I've updated my previous post with the revision.

[#7]

thank you very much Patrick. It looks excellent. You are very talented.

[#8]

You're welcome! Thank you for the kind words.