Dynamic calculation of tenure based on selected month slicer
I have a data that looks like this. A person has multiple rows of data. Each event (such as promotion, hire, termination, rehire) equates to one row of data. I need to get each of their tenure and then add group/bin them accordingly.
If the current status is active then Newest Employment Date - Maximum Date based on selected slicer in the report. (if the slicer selected is Mar 2022, the max date is Mar 31 2022). Thus, Newest employment date - mar 31 2022. The max date adjusts based on the selected slicer.
If the current status is terminated then newest employment date - termination date. If the selected slicer is less than the termination date then use that lesser date (because technically, it makes sense that way)
Then I should group them by 0-6 Months, 1-5 years, 6-10 years and so on and by their teams as well. Result should be a table that looks like this: