Calculate Difference between 2 dates based on a Year slicer

[TBensen]

Hello,

I'm trying to figure out the best way to calculate the difference between 2 year dates based on a yearly slicer, and then have the calculation results be placed into predefined bins.

Here is some background on the tables I am working with.

Employee Table

Emp_ID	Emp_REcord_ID	HireDate	TermDate
123	0	5/8/1984
234	0	9/8/2019
345	0	7/5/2018	8/10/2021

Incident Table

Emp_ID	Date of Incident
123	2/9/2000
345	9/5/2020

BinSelect

SeniorityRange	MinValueRange	MaxValueRange	Index
0 - 1.9 Years	0	1	1
2 - 4.9 Years	2	4	2
36 - 40.9 Years	36	40	9

When I select a Year, let's say 2020, I want to calculate the difference between the HireDate.YEAR and the year selected (2020). This should give me 37, and this number should fall into the 36 - 40.9 Years bin.  All employees, regardless of if they show up in the Incident table should be counted and placed into their respective "Seniority" bins based on the year selected.   

Thank You,

Trevor

[Anonymous]

So you should just change the year slicer with the column of the Incidents table and change that in the measures, or that's not what you are looking for? 

What is still pending would be adding maybe two more columns in the BinSelect table: "number of incidents" and "rate", bacause I understand that the "number of people" would be the same as the "RangeIndex".  

[TBensen]

Hello,

I made the changes you recommended and it looks like it is placing the employees into buckets, but the buckets don't appear to be adjusting based on the year of the Date of Incident that is selected.  

I've also realized that it is including employees that have been termed.  Some of the employees were termed before the Year that is selected, so those employees should not be included in the employee counts.  I tried to add in some DAX to exclude those employees, but it wasn't working. 

Thank you for your help on this.

-Trevor

[Anonymous]

Hi @TBensen ,

For what I see, there's no need to use the Incident Table for this scenario.

I also assume that "MinValueRange" is the minimum year difference identified for any employee on that range, the same idea for the "MaxValueRange", and the "RangeIndex" is the total amount of employees that are in that range.

I created three tables: "Employees", "BinSelect" and "Calendar", and used three measures to achieve what you are looking for.

See the file: https://1drv.ms/u/s!ApEQfav9p1cQjivnqaCIb-Ly_9g_?e=DS5qQF

MEASURE 1:
MinValueRange = 
MINX(FILTER(ADDCOLUMNS(Employees,
                       "Seniority_Range_FilteredYear",
                       MAX('Calendar'[Year])-YEAR(Employees[Hire_Date])
            ),
            [Seniority_Range_FilteredYear]>=MAX(BinSelect[Min]) && 
            [Seniority_Range_FilteredYear]<=MAX(BinSelect[Max])
    ),
    [Seniority_Range_FilteredYear]
)

MEASURE 2:
MaxValueRange = 
MAXX(FILTER(ADDCOLUMNS(Employees,
                       "Seniority_Range_FilteredYear",
                       MAX('Calendar'[Year])-YEAR(Employees[Hire_Date])
            ),
            [Seniority_Range_FilteredYear]>=MAX(BinSelect[Min]) && 
            [Seniority_Range_FilteredYear]<=MAX(BinSelect[Max])
    ),
    [Seniority_Range_FilteredYear]
)

MEASURE 3:
RangeIndex = 
COUNTROWS(FILTER(ADDCOLUMNS(Employees,
                           "Seniority_Range_FilteredYear",
                           MAX('Calendar'[Year])-YEAR(Employees[Hire_Date])
                ),
                [Seniority_Range_FilteredYear]>=MAX(BinSelect[Min]) && 
                [Seniority_Range_FilteredYear]<=MAX(BinSelect[Max])
         )
)

Jesus.

[TBensen]

Hello Jesus,

Thank you for your response.  One of the reasons I included the Incident table is because there is a date (Year) slicer setup in the dashboard to allow the user to select the Year for the Date of Incident.  When this year date is selected, there is a visualization that shows the counts of employees in their respective ranged bins based on the Year of the Date of Incident. 

To further complicate this, I'm also taking the number of incidents for that year per population bin and dividing that by the number of people per population bin to get a rate.

Thanks,
Trevor