Calculate the Amount as Sum of Actual and Forecast depending on Month selected in Story Filter

[amrita_goswami]

Hi All,

We have a requirement to show the Total Amount for a year as the Sum of Actual and Forecast Amount in a table column.

The story has a Date filter. If user picks June 2021, then the Measure should show Actuals from Jan - June 2021 and Forecast from June 2021 to Dec 2021.

I have created a restricted measure for the Actual Data that works based on YTD. However for the Forecast data I am not able to dynamically set the Look Ahead range based on user selection till the end of the year.

IS there any way to achieve the Look ahead months dynamically ? This should vary based on User selection of Months.

[amrita_goswami · Accepted solution]

In this case I was able to resolve the issue by creating one KF for Actuals YTD (till the user selected date) + (Budget full year - Budget YTD(Till the user selected Date))

In this case if user picks Oct 2021, then we have Actuals (Jan- Oct 2021) + (Budget 2021 full Year - Budget (Jan - Oct 2021)). This gives the Projected data for the full year.

[William_Yu]

Hi amrita.goswami,

Looks like it's typical forecast/rolling forecast layout requirement, please follow this help page for detail. Where you can also have a sum column summing the year total.

Best regards, William

[amrita_goswami]

Thanks for this William!