Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TBensen
Helper I
Helper I

Calculate Difference between 2 dates based on a Year slicer

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_IDEmp_REcord_IDHireDateTermDate
12305/8/1984 
23409/8/2019 
34507/5/20188/10/2021

 

Incident Table

Emp_IDDate of Incident
1232/9/2000
3459/5/2020

 

BinSelect

SeniorityRangeMinValueRangeMaxValueRangeIndex
0 - 1.9 Years01

1

2 - 4.9 Years24

2

36 - 40.9 Years3640

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

4 REPLIES 4
Anonymous
Not applicable

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".  

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
Not applicable

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.

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.