I have a situation where I am trying to show how the Headcount moved by comparing Current Month Headcount with any of the historical Month which the user will select. The problem that I am facing is that when any period gets selected Current Month Headcount calculation becomes Zero. My dataset is very complex and massive but just to keep the focus on the key issue, I am sharing a sample data table below.
In the above Data table image,
Column Ref row is to help explain the formula
Filter Option is what the user will choose. I have added it here for easy understanding
Date and Headcount rows are from the Dataset.
Calculation based on user selection:
- If user Select Filter Option as '30 Days Prior' then Headcount Change =
Column B - Column A
and Headcount Change % = (Column B - Column A) / Column A
- If user Select Filter Option as '60 Days Prior' then Headcount Change =
Column C - Column A
and Headcount Change % = (Column C - Column A) / Column A
- If user Select Filter Option as '90 Days Prior' then Headcount Change =
Column D - Column A
and Headcount Change % = (Column D - Column A) / Column A
- If user Select Filter Option as '120 Days Prior' then Headcount Change =
Column E - Column A
and Headcount Change % = (Column E - Column A) / Column A
- If user Select Filter Option as '150 Days Prior' then Headcount Change =
Column F - Column A
and Headcount Change % = (Column F - Column A) / Column A
- If user Select Filter Option as '180 Days Prior' then Headcount Change =
Column G - Column A
and Headcount Change % = (Column G - Column A) / Column A
- If user Select Filter Option as '210 Days Prior' then Headcount Change =
Column H - Column A
and Headcount Change % = (Column H - Column A) / Column A
Will appreciate if someone can help me with this.