I am working on a Headcount report and in section Forecast (happening each month for the following 6 quarters) I am aiming for a Total calculation that contains the Actuals values from one particular month (in my example Type = “Headcount”) and the Forecast values (Type = “FC”) from the same month.
The challenge kicks in, when I have a person coming up with both Type = “Headcount” and Type = “FC” - regardless if the value Employee FTE Equivalent = 1 or = 0.
If this is the case, I want only the value of Type = “FC” to be considered in the totals.
Total calculation should be:
If Type = “Headcount” => Total = sum(Headcount) + sum(Forecast)
If Type = “Headcount” & " FC" => Total = sum (Forecast) + sum (Headcount but excluding those employees that are also listed in the forecast).
I have attached a simple model that showcases what I have done so far:
I came up with a Total calculation of Type = “FC” - in the attached called “FC Type FC Treatas”; the result is displayed in the chart right top
I came up with a Type = “Headcount” calculation - “FC Type Headcount”. Unfortunately not usable for the Totals since I would have double counting for the employees that are in both Types. The numbers are in the chart right middle.
The calculation for Headcount excluding the employees that are counted in the forecast “FC Type Headcount (excl FC)” is not ideal and I’d appreciate suggestions, assistance with it since even though it seems to bring the right result per row, it does not give me a total and thus the end result is not correct. The result is represented in the bottom chart right.
The left chart shows the numbers of the Total calculations and as you can see when changing to different month or different FC quarters in the filters, the totals are not correct.
Look forward to receiving your suggestions on how to solve this topic.
Thank you in advance for taking the time to look into this.
Regards,
Paula
While waiting for other members to jump in, I suggest using the forum search to discover if your query has been asked before by another member. Thanks!
Bumping this post for more visibility for other users.
A possible solution is to separately categorize the six forecasts together with the actual, to be able to calculate the FTE totals for actual and all forecast periods with a single FTE measure.
In my opinion it is necessary to distinguish the forecasts to report FTE.
Condition is that it is possible to adjust the fact table “Combined Allocation FC” as described below (showstopper)!
Adjust / add information to the fact table “DS Combined Allocation FC” in 4 columns, see green arrows and lines:
Rename column “Month” to “Month Version”, to make it clearer it is a new monthly version of the forecast and actual data.
Adjust the “DS Type” column, replacing “Headcount” for “Actual”, and categorize the generic “FC” into “FC 01” to “FC 06”, to be able to report FTE-numbers for those different forecasts and actual.
Add in column “Forecast Quarter” the word “Actual” where “Actual” is added in column “DS Type” (before was nothing), this is done to be able to show valid headers in a report.
Add in column “DS Act FC Qtr Start Date” the “Month Version”-date (before was nothing), , this is done to be able to show valid headers in a report.
Assuming that the live fact table data has the same columns as the provided sample: with the described adjustments above the fact table has all the information needed for the reporting as required, and therefore the two date tables are not needed and can be deleted.
(However it is thinkable to create a lookup table from the forecast data provided the fact table, and delete some forecast data information out of the fact table)
A single measure is sufficient for reporting with the adjustments as described above:
The challenge kicks in, when I have a person coming up with both Type = “Headcount” and Type = “FC” - regardless if the value Employee FTE Equivalent = 1 or = 0.
If this is the case, I want only the value of Type = “FC” to be considered in the totals.
It is confusing to understand what you exactly want to see.
Reaction on your comment " Total should be 2: 1 for AH from Actual and 1 for CC from FY22Q4_FC ":
Why would you count the Actual value of Audrey Hepburn of 1.01 from July 2021 into FC 01/07/2022 in the FTE reporting as she is not forecasted in that period (only Charlie Chaplin is forecasted on V08004 for that FC-period) ?
Could you further define your request ? What report do you want to see ?
“Headcount” (Actual) has a date and the six forecasts have six dates, which give in my opinion seven FTE totals at seven different dates.
Your example: Audrey Hepburn works in the July 2021 monthly update 101% on CC V080A6, and does not work in any of the six forecasts, do you in this case want the Actual reduced with 1.01 ?
What to calculate if AH is forecasted to work on CC V080A6 again in the fourth forecast period, eg 01.04.2022 ?
Or do you want the first forecast be leading over the actual, if for July 2021 FY21 Q4_FC is nihil for Audrey Hepburn and Actual is 1.01, you want to show the first forecast value, leaving all other forecast periods unchanged ?
Do you need a column with Actual FTE at all, could not simply be taken the first forecast if that would appear to be leading data ? On the other side, when looking at the May 2021 monthly update, it seems to make sense to present the actual May FTE numbers, comparing to the first forecast from 01.04.2021.
What is your exact definition of “the totals”, given six forecast periods and an actual ?
Hi Jan,
it looks like I am not making myself clear enough, I apologize for this.
When forecasting in a month for the next six quarters, I am assuming that all my actuals will still be with the company during those 6 month and their FTE will be the same, unless we mirror changes in the Forecast for one particular employee - this is when the Type for the employee will be “Headcount” and “FC” and those rows will have values for the 6 quarters and the actual month = the month when we are reporting.
The Datelookup table was introduced to assist with providing the same granularity for actuals and FC for a particular month.
My Total Forecast per month contains the Actuals + Forecast - but if an Employee is in the Forecast, it can’t be counted twice, but will be counted only from the Forecast.
The aim is to visualize a dynamic Total Forecast each month when I have actuals for each according 6 quarters.
Thanks for the explanation.
If I understand you correctly: you just want the forecast periods filled with actual, when totally no forecast is given for an employee (in a Month version); otherwise use forecast figures, even if they were 0.
I try to work out a DAX -code including the requested context change when needed, for the forecast figures, based upon this concept.
It’s great to know that you are making progress with your query @paula.schneider.
Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’.
We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey, We hope you’ll give your insights on how we can further improve the Support forum. Thanks!
Added a calculated column “Forecast” in the fact-table to be able to distinguish/calculate separately the six forecasts (1-6) (per month-version), calculated upon the days difference between the columns “Month” and “FC Qtr Start” , and Actual (0) when “Type” is “Headcount”
Created a measure “Detect FC” to determine whether a Forecast exists per employee.
Detect FC =
Var Employee3 = SELECTEDVALUE( 'Combined Allocation FC'[Employee ID])
Var Detect2 =IF( Employee3 <> BLANK(),
COALESCE(
COUNTROWS(
FILTER( SUMMARIZE( 'Combined Allocation FC','Combined Allocation FC'[Employee ID], 'Combined Allocation FC'[Month],'Combined Allocation FC'[Type] ),
'Combined Allocation FC'[Type] = "FC" ) ),//outcome 1 if any FC per employee per Month version
9999 ) //no Forecast
,77777) //no employee
return Detect2
Create seven measures (Actual and Forecast 1-6) to calculate the FTE, whereby Actual is used in the forecast if no forecast is given.
Note: measures of step 2 till 4 can be found in the measure group “Measures DS”
Created measures (measure group “Measures headers”) to be able to present some dynamic headers with more meaningful text. (obviously more could be created), which are added as cards above the columns in the example report.
Note: six separate measures for the forecasts are needed to be able to add Actual to the forecasts, when no forecast is entered.
It is NOT possible to show Actual added to Forecasts, AND using the column “Forecast Quarter” (eg. in the header in a visual), because the chosen context prevents showing Actual values when they do not exist, no values will be shown (e.g. Audrey Hepburn in FC July).
Therefore to present correct FTE-values using the concept, six measures are needed, and further separate measures has to be added for better readable headers.
Note: with this solution, there is no need for the tables “Date Lookup” and “Dates”.
(However it is thinkable to create a lookup table from the forecast data provided the fact table, and delete some forecast data information out of the fact table)
Hi @paula.schneider, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.
Hi Jan,
I apologize for not responding earlier, I was very caught up.
Thank you for your response and taking the time.
When working through your suggestions, I found that when filtering on “Month” the result for Charlie Chaplin does not pull the correct FC numbers in May and July - possible because the FTE number is not the same for all the 6 forecast quarters?
Hi @paula.schneider , VERSION 2, with shorter and corrected (total) Forecast 1-6 formulas,
Apologizes, you are right, I checked the 6 total measures, “Forecast1-6”, and everywhere it was totaling “FC 1”, forgot to change this measure by copying. It should have been FC 1, FC 2, FC 3, FC 4, FC 5 and FC 6 respectively in the 6 total measures.
Secondly, “Forecast 1-6” is to be calculated by forced line iteration in the fact table “Combined Allocation FC”, no additional virtual table needed, therefore the formula should be (as no need for “Addcolumns”) :
Please receive hereby an adjusted PBIX with the correct totals and short total formulas, I should have checked a bit more. FC Alloc excl HC 25.11.2021 v5.pbix (204.1 KB)
