Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all,
Im facing the following problem:
I have a table with employees and their start and enddate of their contract (if blank then still active). See example below:
EmployeeNoEmployee Contract startEmployee Contract end
10500 | 1-11-2017 | |
10550 | 1-12-2017 | 30-9-2018 |
10600 | 1-1-2018 | 31-12-2018 |
10650 | 1-2-2018 | 31-12-2018 |
10700 | 1-3-2018 | |
10750 | 1-4-2018 | |
10800 | 1-5-2018 | |
10900 | 1-4-2018 | |
10950 | 1-2-2018 | 31-1-2019 |
11560 | 1-8-2018 | |
11900 | 1-9-2018 |
Next I have a table with absence data, per absence occurence (from start to end) i have the start time of absence and the end time of the absence (if blank, then still absent).
AbsenceKeyEmployeeNoAbsence startAbsence end
1 | 10500 | 1-1-2018 | 15-11-2018 |
2 | 10600 | 5-3-2018 | 10-8-2018 |
3 | 10900 | 6-3-2018 | |
4 | 10950 | 10-4-2018 | 11-4-2018 |
5 | 11560 | 1-11-2018 | 5-2-2019 |
6 | 11900 | 25-2-2019 | |
7 | 10950 | 15-5-2018 | 25-8-2018 |
What I would like to check, is the employees who have not been sick in the last twelve months, per month.
Required outcome example:
MonthEmployeeCount
nov-17 | 10500 | 1 |
dec-17 | 10500 | 1 |
jan-18 | 10550 | 1 |
feb-18 | 10550 | 1 |
mrt-18 | 10550 | 1 |
apr-18 | 10550 | 1 |
mei-18 | 10550 | 1 |
jun-18 | 10550 | 1 |
jul-18 | 10550 | 1 |
aug-18 | 10550 | 1 |
sep-18 | 10550 | 1 |
jan-18 | 10600 | 1 |
feb-18 | 10600 | 1 |
….. |
and so on.
Solved! Go to Solution.
Hi @VV24
Try this (see it at work in the file attached)
1. Create a Calendar table so that we can show the month-year as requested:
CalendarT = ADDCOLUMNS ( CALENDAR ( DATE ( 2016; 01; 01 ); DATE ( 2019; 12; 31 ) ); "MonthYear"; FORMAT ( [Date]; "mmm" ) & "-" & YEAR ( [Date] ); "MYN"; YEAR ( [Date] ) * 100 + MONTH ( [Date] ) )
2. Create a calculated table:
ResultTable = ADDCOLUMNS ( CROSSJOIN ( CALCULATETABLE ( SUMMARIZE ( CalendarT; CalendarT[MonthYear]; CalendarT[MYN] ); YEAR ( CalendarT[Date] ) >= 2017 ); DISTINCT ( Employee[EmployeeNo] ) ); "_Flag"; VAR _FirstMYN = CALCULATE ( MIN ( CalendarT[MYN] ); FILTER ( CalendarT; CalendarT[MYN] > EARLIER ( [MYN] ) - 100 ) ) VAR _LastMYN = [MYN] VAR _Last12Months = CALCULATETABLE ( DISTINCT ( CalendarT[Date] ); FILTER ( CalendarT; CalendarT[MYN] >= _FirstMYN && CalendarT[MYN] <= _LastMYN ) ) VAR _FirstDayLast12Months = FIRSTNONBLANK ( _Last12Months; 1 ) VAR _LastDayLast12Months = LASTNONBLANK ( _Last12Months; 1 ) VAR _EmployeeStartDate = LOOKUPVALUE (Employee[Employee Contract start]; Employee[EmployeeNo]; [EmployeeNo]) VAR _EmployeeStartMY = LOOKUPVALUE ( CalendarT[MYN]; CalendarT[Date]; _EmployeeStartDate ) VAR _EmployeeEndDate = LOOKUPVALUE ( Employee[Employee Contract end]; Employee[EmployeeNo]; [EmployeeNo] ) VAR _EmployeeEndMY = LOOKUPVALUE ( CalendarT[MYN]; CalendarT[Date]; _EmployeeEndDate ) VAR _EmployedLast12Months = _EmployeeStartDate <= _FirstDayLast12Months && ( _EmployeeStartDate >= _LastDayLast12Months|| ISBLANK ( _LastDayLast12Months )) VAR _AbsenceTableCurrentEmployee = ADDCOLUMNS ( FILTER ( Absence; Absence[EmployeeNo] = EARLIER ( [EmployeeNo] ) ); "_Absent?"; COUNTROWS ( INTERSECT ( GENERATESERIES ( Absence[Absence start]; IF ( ISBLANK ( Absence[Absence end] ); _LastDayLast12Months; Absence[Absence end] ) ); _Last12Months ) ) > 0 ) VAR _WasAbsent = SUMX ( _AbsenceTableCurrentEmployee; [_Absent?] * 1 ) > 0 RETURN IF ( NOT _WasAbsent && (_EmployeeStartMY <= [MYN] && ( _EmployeeEndMY >= [MYN] || ISBLANK ( _EmployeeEndMY ))); 1; 0 ) )
3. Place ResultTable[EmployeeNo], ResultTable[MonthYear] and ResultTable[_Flag] in a table visual. Make sure they all are displayed with 'Don't summarize'
To wrap it up, review the behavior at the edges as it might not be exactly what you want. You can update the code needed.
Cheers
Hi @VV24
Try this (see it at work in the file attached)
1. Create a Calendar table so that we can show the month-year as requested:
CalendarT = ADDCOLUMNS ( CALENDAR ( DATE ( 2016; 01; 01 ); DATE ( 2019; 12; 31 ) ); "MonthYear"; FORMAT ( [Date]; "mmm" ) & "-" & YEAR ( [Date] ); "MYN"; YEAR ( [Date] ) * 100 + MONTH ( [Date] ) )
2. Create a calculated table:
ResultTable = ADDCOLUMNS ( CROSSJOIN ( CALCULATETABLE ( SUMMARIZE ( CalendarT; CalendarT[MonthYear]; CalendarT[MYN] ); YEAR ( CalendarT[Date] ) >= 2017 ); DISTINCT ( Employee[EmployeeNo] ) ); "_Flag"; VAR _FirstMYN = CALCULATE ( MIN ( CalendarT[MYN] ); FILTER ( CalendarT; CalendarT[MYN] > EARLIER ( [MYN] ) - 100 ) ) VAR _LastMYN = [MYN] VAR _Last12Months = CALCULATETABLE ( DISTINCT ( CalendarT[Date] ); FILTER ( CalendarT; CalendarT[MYN] >= _FirstMYN && CalendarT[MYN] <= _LastMYN ) ) VAR _FirstDayLast12Months = FIRSTNONBLANK ( _Last12Months; 1 ) VAR _LastDayLast12Months = LASTNONBLANK ( _Last12Months; 1 ) VAR _EmployeeStartDate = LOOKUPVALUE (Employee[Employee Contract start]; Employee[EmployeeNo]; [EmployeeNo]) VAR _EmployeeStartMY = LOOKUPVALUE ( CalendarT[MYN]; CalendarT[Date]; _EmployeeStartDate ) VAR _EmployeeEndDate = LOOKUPVALUE ( Employee[Employee Contract end]; Employee[EmployeeNo]; [EmployeeNo] ) VAR _EmployeeEndMY = LOOKUPVALUE ( CalendarT[MYN]; CalendarT[Date]; _EmployeeEndDate ) VAR _EmployedLast12Months = _EmployeeStartDate <= _FirstDayLast12Months && ( _EmployeeStartDate >= _LastDayLast12Months|| ISBLANK ( _LastDayLast12Months )) VAR _AbsenceTableCurrentEmployee = ADDCOLUMNS ( FILTER ( Absence; Absence[EmployeeNo] = EARLIER ( [EmployeeNo] ) ); "_Absent?"; COUNTROWS ( INTERSECT ( GENERATESERIES ( Absence[Absence start]; IF ( ISBLANK ( Absence[Absence end] ); _LastDayLast12Months; Absence[Absence end] ) ); _Last12Months ) ) > 0 ) VAR _WasAbsent = SUMX ( _AbsenceTableCurrentEmployee; [_Absent?] * 1 ) > 0 RETURN IF ( NOT _WasAbsent && (_EmployeeStartMY <= [MYN] && ( _EmployeeEndMY >= [MYN] || ISBLANK ( _EmployeeEndMY ))); 1; 0 ) )
3. Place ResultTable[EmployeeNo], ResultTable[MonthYear] and ResultTable[_Flag] in a table visual. Make sure they all are displayed with 'Don't summarize'
To wrap it up, review the behavior at the edges as it might not be exactly what you want. You can update the code needed.
Cheers
Thanks AIB,
I tried to rebuild it with, with real data but i get an error saying that "the arguments in the GenerateSeries function cannot be blank."
Do you know how this comes?
It's probably the first argument, i.e. the absence start date. Are there any blanks? I assumed there wouldn't.
What does a blank in the absence start date mean? What is the expected behavior? One option would be doing a check on the first argument similar to what we already do in the second argument):
GENERATESERIES ( IF ( ISBLANK ( Absence[Absence start] ); _FirstDayLast12Months; Absence[Absence start] );
IF ( ISBLANK ( Absence[Absence end] ); _LastDayLast12Months; Absence[Absence end] ) );
You can modify that depending on the behavior you want. It might be wise to eliminate blanks in the original data altogether.
Otherwise, if you share your real data I'll try to have a look.
So we are getting there! It does work now, one problem iam facing is that my datetable starts at year 2000, so all the employees even those with a contract start date later than 2000 are in a row per month (with flag = 0), but I only want to have a row if their contract started.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |