Forum Discussion
Show a Variance
Revised workbook attached.
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)
- 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?
Revised workbook attached.
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.
