Forum Discussion
Helper IIIAutomatically calculate data on rolling quarterly basis
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:
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.
5 Replies
- amitchandak
Super User
Refer, if this can help
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])))
tobiasmcbrideThanks 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?
