cancel
Showing results 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

Frequent Visitor

## employee headcount calculation and leaver count YTD

Hi Community Member,
I have come up with a problem with creating a table in Power BI for employee headcount calculation and leaver count YTD.
In this, we have to show data w.r.t India financial year i.e. starting from April.

Here is the list of all formula used in Excel.
Active /InActive:- IF(AND(DOJ <=\$P\$2, OR(DOL > \$P\$2, ISBLANK(DOL))), "Active", "Inactive")
Leavers in current FY:- =IF(AND(DOL<=\$P\$2, DOL >= DATE(2023,4,1)), 1, 0)

Closing HC as of Selected Month :- COUNTIF(Active /InActive,"Active")
Leavers as of Selected Month:- COUNTIF(Leavers in current FY,1)

\$P\$2 = DATE(2023,11,30)

INPUT DATA(sample 100 rows)

 Employee Code DOJ DOL Active /InActive Leavers in current FY 107117 3-Jan-22 27-Jun-22 Inactive 0 107164 31-Jan-22 Active 0 107167 1-Feb-22 31-Oct-23 Inactive 1 107539 2-Jan-23 Active 0 107540 2-Jan-23 Active 0 107542 5-Jan-23 Active 0 107544 9-Jan-23 5-Jun-23 Inactive 1 107565 27-Jan-23 Active 0 107564 27-Jan-23 Active 0 107566 30-Jan-23 Active 0 107574 1-Feb-23 Active 0 107583 13-Feb-23 Active 0 107597 20-Feb-23 Active 0 107170 8-Feb-22 8-Feb-23 Inactive 0 107599 21-Feb-23 10-Dec-23 Active 0 107604 27-Feb-23 11-Dec-23 Active 0 107175 10-Feb-22 Active 0 107624 14-Mar-23 Active 0 107628 15-Mar-23 Active 0 107625 16-Mar-23 31-Jul-23 Inactive 1 107629 16-Mar-23 26-Dec-23 Active 0 107633 20-Mar-23 26-May-23 Inactive 1 107617 20-Mar-23 Active 0 107618 3-Apr-23 Active 0 107639 3-Apr-23 Active 0 107186 28-Feb-22 Active 0 107189 1-Mar-22 Active 0 107192 2-Mar-22 6-Jul-22 Inactive 0 107216 1-Apr-22 Active 0 107215 1-Apr-22 Active 0 107217 4-Apr-22 13-May-22 Inactive 0 107224 2-May-22 Active 0 107225 2-May-22 Active 0 107255 7-Jun-22 6-Apr-23 Inactive 1 107257 8-Jun-22 Active 0 107650 3-Apr-23 20-Sep-23 Inactive 1 107282 1-Jul-22 Active 0 107287 4-Jul-22 Active 0 107321 28-Jul-22 10-Apr-23 Inactive 1 107320 1-Aug-22 Active 0 107323 1-Aug-22 Active 0 107669 2-May-23 Active 0 107684 22-May-23 Active 0 107692 22-May-23 25-Sep-23 Inactive 1 107693 22-May-23 Active 0 107752 1-Jun-23 Active 0 107756 1-Jun-23 Active 0 107714 5-Jun-23 10-Nov-23 Inactive 1 107726 5-Jun-23 30-Dec-23 Active 0 107695 5-Jun-23 Active 0 107374 23-Aug-22 10-Oct-22 Inactive 0 107377 23-Aug-22 23-Mar-23 Inactive 0 107373 23-Aug-22 Active 0 107383 1-Sep-22 20-Jan-23 Inactive 0 107370 1-Sep-22 Active 0 107779 23-Jun-23 21-Aug-23 Inactive 1 107698 24-Jul-23 Active 0 107797 24-Jul-23 Active 0 107796 24-Jul-23 Active 0 107798 3-Aug-23 Active 0 107805 28-Aug-23 Active 0 107806 29-Aug-23 Active 0 107810 1-Sep-23 Active 0 107808 4-Sep-23 Active 0 107809 4-Sep-23 Active 0 107817 6-Oct-23 Active 0 107816 16-Oct-23 Active 0 107818 19-Oct-23 Active 0 107819 6-Nov-23 Active 0 107820 6-Nov-23 1-Dec-23 Active 0 107821 21-Nov-23 Active 0 107823 21-Nov-23 Active 0 107824 26-Dec-23 Inactive 0 107459 28-Oct-22 Active 0 107466 31-Oct-22 Active 0 107462 1-Nov-22 Active 0 107463 2-Nov-22 Active 0 107499 1-Dec-22 31-Oct-23 Inactive 1 107531 27-Dec-22 Active 0 107526 29-Dec-22 Active 0 107523 29-Dec-22 Active 0 107530 30-Dec-22 Active 0 107529 30-Dec-22 6-Feb-23 Inactive 0 107528 30-Dec-22 Active 0

OUTPUT TABLE

 Row Labels Closing HC as of Selected Month Leavers as of Selected Month Apr-23 47 2 May-23 50 3 Jun-23 55 4 Jul-23 57 5 Aug-23 59 6 Sep-23 60 9 Oct-23 61 10 Nov-23 64 11

1 ACCEPTED SOLUTION
Super User

Hi @mojain ,

For this, you need to create a separate dates table with a column that indicates which FY a date belongs to. This is disconnected from (no relationship to ) fact. Then, create these measures:

HC =
VAR __MAX =
MAX ( Dates[Date] )
RETURN
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[DOJ] <= __MAX
&& (
'Table'[DOL] > __MAX
|| ISBLANK ( 'Table'[DOL] )
)
)
)
Leavers =
VAR __MIN =
CALCULATE ( MIN ( Dates[Date] ), ALLEXCEPT ( Dates, Dates[FY] ) )
VAR __MAX =
MAX ( Dates[Date] )
RETURN
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( 'Table', 'Table'[DOL] > __MIN && 'Table'[DOL] <= __MAX )
)

Please see attached pbix for reference.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
2 REPLIES 2
Frequent Visitor

Thank you @danextian for the solution.
It worked.

Super User

Hi @mojain ,

For this, you need to create a separate dates table with a column that indicates which FY a date belongs to. This is disconnected from (no relationship to ) fact. Then, create these measures:

HC =
VAR __MAX =
MAX ( Dates[Date] )
RETURN
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[DOJ] <= __MAX
&& (
'Table'[DOL] > __MAX
|| ISBLANK ( 'Table'[DOL] )
)
)
)
Leavers =
VAR __MIN =
CALCULATE ( MIN ( Dates[Date] ), ALLEXCEPT ( Dates, Dates[FY] ) )
VAR __MAX =
MAX ( Dates[Date] )
RETURN
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( 'Table', 'Table'[DOL] > __MIN && 'Table'[DOL] <= __MAX )
)

Please see attached pbix for reference.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.