Forecast to Forecast Comparison and Forecast to Actuals comparison

[a_rp]

Dear All, I want to create a comparison showing Variance between different versions of Forecast (new version gets released every month) and Forecast Vs Actuals. I have created sample tables showing sample data and also the way I want the comparisons (output report). Appreciate your help & guidance. 

Note: If you show me the logic for getting one of the output columns, I will try to extend that too all remaining columns required in the output table/report.

Thanks & Regards,

[littlemojopuppy · Accepted solution]

Hi @a_rp.  This shouldn't be difficult to do, if you change your tables.  You have a column for each month.  If you change the structure so that they are like this:

• Forecast/Actual (values would be Actual, Forecast 1, Forecast 2, etc.)
• Portfolio
• Project
• Financial (Revenue, Cost, Profit)
• Gurantee
• Month Ending (use date values!)
• Amount

The formulas would be simple...this would be the formula for Actual

CALCULATE(
	SUM([Amount]),
	[Forecast/Actual] = "Actual"
)

Modify as appropriate for each of your other forecast measures.

And make sure you add a date table (and mark it appropriately) and create a relationship between the date table and Month Ending.

Hope this helps!

[littlemojopuppy · Accepted solution]

That's easy...use the TOTALQTD function.  Time intelligence functions (such as this one) require a date table (mentioned previously).  Make sure you use the date frield from the date table in the function.

[a_rp]

Thank you 🙂 Can you plz share how to calculate Q1, Q2, etc. shown in my output table? (one example will be helpful).

[littlemojopuppy · Accepted solution]

That's easy...use the TOTALQTD function.  Time intelligence functions (such as this one) require a date table (mentioned previously).  Make sure you use the date frield from the date table in the function.

[littlemojopuppy]

Hi @a_rp ...how'd this work?

[a_rp]

Hi @littlemojopuppy the solution is working perfectly. Many thanks for the same. I need help on Date Table.....as my Fiscal Year is not the same as a calendar year, I want to redefine quarters in the Quarter column but it's not working. Can you plz help?

[littlemojopuppy · Accepted solution]

Hi @a_rp.  What you want to do is create a series of additional fields to accommodate fiscal calendar.

Here's something I use (and actually updated this morning).  There's a bunch of fields that begin with "Calendar...".  Those are based on the actual calendar.  The fields that begin with "Reporting..." are effectively the fiscal calendar.  Check the values to make sure it aligns with your calendar.

If you agree that I answered your original question, please mark it as a solution so others might be able to benefit from it.  And let me know if I can help further!

[a_rp]

Hi @littlemojopuppy Apologies for the late response. I couldn't implement/respond earlier for personal matters. I really appreciate your help. Your replies helped me greatly. Thanks again.

[littlemojopuppy]

@a_rp de nada!  Glad I could help!

[littlemojopuppy · Accepted solution]

Hi @a_rp.  This shouldn't be difficult to do, if you change your tables.  You have a column for each month.  If you change the structure so that they are like this:

• Forecast/Actual (values would be Actual, Forecast 1, Forecast 2, etc.)
• Portfolio
• Project
• Financial (Revenue, Cost, Profit)
• Gurantee
• Month Ending (use date values!)
• Amount

The formulas would be simple...this would be the formula for Actual

CALCULATE(
	SUM([Amount]),
	[Forecast/Actual] = "Actual"
)

Modify as appropriate for each of your other forecast measures.

And make sure you add a date table (and mark it appropriately) and create a relationship between the date table and Month Ending.

Hope this helps!