How to handle Fiscal Year in QuickSight dynamically?

[Vaibhav_Patidar]

Hi experts,

I have to prepare “Employee Attrition dashboard” in quicksight and I need the dynamic calculation based on the Parameters, but as ‘StartOfthisYear’ parameter takes default start date as 01-01-YYYY (2025), I am unable to display data based on Fiscal Year (April-March)
But I have already done something which is explained in below calculations.

Data fields are like:
-Emp Code,
-Name,
-Date of Joining,
-Last Working Date,
-Attrition (having values ‘0’ and ‘1’, 0 represents “Active”, and 1 represents “Attrited”)
-Zone, Region, State, Location etc are some other fields which I need to use as filters and for other KPIs.

The logic to calculate Attrition Rate is:

1. Number of employees beginning of the year/month/quarter – [A]
2. Number of employees left during the year/month/quarter – [B]
3. Number of new employees joined during the year/month/quarter – [C]
4. Number of employees at the end of the year/month/quarter – [D]
   Formula to calculate [D]: [A]-[B]+[C]
5. Total Active Base Head Count at the end of the year/month/quarter – [E]
   Formula to calculate [E] = ([A] + [D]) / 2
   Formula to calculate Attrition Rate: ([B] / [E]) * 100

Parameters and dependent calculations:
${startOfYear} - represents start of this year.
${endOfYear}- represents End of this year.

{DOJ_FY}: addDateTime(-3,‘MM’, {Date of Joining})
{LWD_FY}: addDateTime(-3,‘MM’, {Last Working Date})

I have tried calculating A, B, C, D, E as per logic shared above with below calculated fields:

A:
countIf({Emp Code}, {DOJ_FY} < ${startOfYear} AND (Attrition = 0 ))

B:
countIf({Emp Code},{LWD_FY}>=${startOfYear} AND {LWD_FY}<=${endOfYear} AND Attrition = 1)

C:
countIf({Emp Code},{DOJ_FY}>=${startOfYear} AND {DOJ_FY}<=${endOfYear})

D:
{A_}-{B_}+{C_}

E:
({A_} + {D_}) /2
Here, I have divided by 2 just to have an average headcount.

Please note that whenever I have Active users, My Last Working Date remains NULL and for that employee I have Attrition as 0 as he is Active into the system.
Can someone help me in this approach of mine in handling Fiscal Year is the correct approach or should I make some changes ?

Best,
Vaibhav

[Brett]

Hi @Vaibhav_Patidar,
From my experience when working with a situation like this, I have used similar logic. Is there something that’s not working correctly in your calculations or are you just checking to see if there’s an easier way to handle this type of scenario?

[Giridhar.Prabhu]

Hi @Vaibhav_Patidar,

I would keep things simple.

I would rather calculate Fiscal Start Date and Fiscal End Date as calculated fields with
“01/04/yyyy” and “31/03/yyyy” (refer parseDate function). yyyy comes from your parameter.

You can make it clear to the users that they are only selecting the Fiscal Year. Day and Month from their selection are irrelevant.

Rest of your computations to be adjusted based on the above two dates.

Regards,
Giri

[Vaibhav_Patidar]

Hi @Brett
Yes I am looking for the easier approach to handle this specific use case.

I have tried the valuable inputs provided by @Giridhar.Prabhu
I have created the dynamic SOY and EOY from my parameter and linked it with my calculations of A,B,C,D and E.

This is what I created:
Parameters (which was there already): startOfYear and endOfYear

Calculated fields to have dynamic start and end of FY (April-March):

Dynamic_startOfYear_FY:

ifelse(
extract(“MM”, ${startOfYear}) >= 4,
parseDate(concat(toString(extract(“YYYY”, ${startOfYear})), ‘-04-01’), ‘yyyy-MM-dd’),
parseDate(concat(toString(extract(“YYYY”, ${startOfYear}) - 1), ‘-04-01’), ‘yyyy-MM-dd’)
)

Dynamic_endOfYear_FY:

ifelse(
extract(“MM”, ${startOfYear}) >= 4,
parseDate(concat(toString(extract(“YYYY”, ${startOfYear}) + 1), ‘-03-31’), ‘yyyy-MM-dd’),
parseDate(concat(toString(extract(“YYYY”, ${startOfYear})), ‘-03-31’), ‘yyyy-MM-dd’)
)

And I have added this dynamic calculation in my calculations of A,B,C,D,E, something like this:
(Sharing calc only for A)

A:

countIf({Emp Code}, {Date of Joining} < {Dynamic_startOfYear_FY}
AND
(Attrition = 0 ))

I’ve checked that the logic is working well for the counts like below:

But now as this is dynamic, I am unable to show attrition rate for different fields like Zone, Region etc based on Fiscal year under different charts.
I already had one field in my data (Financial Year) where I have years like FY-19-20 etc (shown in below chart)

I need to show something which looks like below:

As the FInancial Year is on X-axis, when I am adding my Attrition Rate calculation in this chart, the chart looks like below:

This shows my attrition is -50%
I understand this is because of the dynamic nature, But how I can solve this ?
Or if you can also suggest what should be the best possible ways I can show Attrition Rate under these different data categories ?

Thanks,
Vaibhav

[Brett]

Hi @Vaibhav_Patidar,
Apologies for missing your last response and the delay. Unfortunately, I’m not aware of any other work around to better handle attrition rate as I’ve used a tactic similar to what Giri suggested above.
If you’re still encountering issues, I would suggest creating a new chain in the community to bring this category back to attention and see if other community members have differing ideas.
For the time being, I’ll close this topic but feel free to link it in your new post if you’d like to include relevant information.

Thank you!