Automatically calculate data on rolling quarterly basis

[tobiasmcbride]

Hi,

I currently have headcount data in a spreadsheet and am calculating attrition. Currently, I calculate it by calculating the numbers who have classified as left the company between two dates (today and a year ago from today) divided by the average of the headcount (calculated by number of established employees) on today's date and that a year ago.

As such the formula is formatted as follows:

Attrition =

Var tdate = TODAY()

Var tyear = YEAR(tdate)

Var tmonth = MONTH(tdate)

Var tday = DAY(TODAY())

return

CALCULATE(COUNTROWS(Headcount),Headcount[Current Status]="left",FILTER(Headcount,Headcount[End Date]<=DATE(tyear,tmonth,tday) && Headcount[End Date]>=DATE(tyear-1,tmonth, tday)))/((CALCULATE(COUNTROWS(Headcount),Headcount[Current Status]="established")+CALCULATE(COUNTROWS(Headcount),Headcount[Current Status]="established", FILTER(Headcount, Headcount[Start Date]<=DATE(tyear-1,tmonth,tday))))/2)

Now, however, rather than calculating it over the last year (which will roll forward automatically) I need to format it such that it calculates over the last quarter. This would be both the last 3 months from today (being able to take into account the change in years so cannot just be tmonth -3) but also the last financial quarter. i.e. as we are in FQ4 now, it would look at FQ3 until we reach the date of end of FQ4 and then calculate FQ4.

I know I need two different formulas to do so (or maybe there's a clever way to use one) but any assistance on this would be greatly appreciated. 

[amitchandak]

Refer, if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[amitchandak]

last 3 month terminate =
CALCULATE(COUNT(Employee[Employee Id ]),DATESINPERIOD('Date'[Date],max(Employee[End Date]),-3,MONTH)),USERELATIONSHIP(Employee[End Date],'Date'[Date]),not(ISBLANK(Employee[End Date])))

[tobiasmcbride]

Thanks for that - won't that formula prove difficult over a year-end? For instance if you took 3 months back from now would give December but the year would remain 2020 rather than change to 2019?

[amitchandak]

This formula will have no impact of year-end.

[tobiasmcbride]

That's great thanks - further to that, I have another column in the dataset which allows me to exclude people from attrition (simply by putting yes in that column). 

How would I enable the function below to only calculate those who are employees who have 'left' as per below in last 3M but who also have a 'yes' in the 'exclude from attrition' column?