employee headcount calculation and leaver count YTD

[mojain]

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

[mojain]

Thank you @danextian for the solution.
It worked.

[danextian · Accepted solution]

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.