Hi Pigment Community!
I wasn’t able to find a prior thread on this:
What’s the best practice to show Months of Actuals and Forecast together based on the Switchover Date?
e.g. I have 6 months, and 4 of them are actuals. I want to show the month and period type in the column titles, then my data:
Sep 23 Oct 23 Nov 23 Dec 23 Jan 23 Feb 23
Actual Actual Actual Actual Forecast Forecast
Row 1 ### ### ### ### ### ###
Best answer by OliverView original
First, you need to provide Date of Month, From you would like to define Forecast.
After that you need to add Month>Period Type in your column by available pivoted option in metric or Table. Screenshot is attached herewith for your immediate reference.
Hope this will help to get the view as you want.
@Trever Leavenworth ,
If you put the period type on top of months in the pivot, I find it easier to identify where you switchover from Actuals to forecasting. You could also add some color to help business users quickly see which period is which. Here is a screenshot with some dummy data.
Ah the Month > Period Type is what I was missing. Thank you both!
In your setup
@Chris , how is Month > Period type structured? I have “Period Type” as a Property of my Month calendar dimension, but I don’t see the ability to add that property as a pivot item?
Or, asked differently, here’s my problem:
Because prior forecast values are preserved in the forecast metric, they aren’t filtered via “hide empty columns” like the actuals are. Here we leave off in Feb 23 with Actuals and pick up in Jan 21 with Forecast.
If you want to add the Month > Period type property in your pivot you need first to remove the Period type dimension from your view because you can’t have the same dimension twice in your view.
If you want to keep the Period type in your view crossed with the month dimension you can then add a filter metric in boolean with both month and period type dimension and add a formula to check Actual months and forecast month like this :
Add this metric to your table and use it as a filter on month dimension.
Another solution would be to create another metric and use a [FILTER : boolean] directly in your formula (you can reuse the Is period type metric) to BLANK Actual months in Forecast period type