Help required to filter prior record from history

[gsrk1982]

Dear All,

I am working in Employee headcount analysis.Headcount will be calculated from employee history values.

I am having Employehistory and Date table.Dateofjoining active relationship and DateofLeaving Inactive relationship.Also I am having effctivedate column.In report I have Date slicer (Between) when slicer selected (Max date on slicer) I need to pass and filter prior values from history . Department and Grade are counted on relavant effective date.Kindly help me how to filter.T-SQL i have achived this.I am new to DAX so struggling to get....

Emp_ID	Effective_From	Department	Designation	Grade	Join Date	Leaving Date	Leaving Status	Record No
1	01-Jan-2016	Product	System Engineer	100	01-Jan-2016	NULL	0	1
1	02-Jan-2017	Proudct	Sr.System Engineer	110	01-Jan-2016	NULL	0	2
1	02-Mar-2018	R&D	Asst.Manager	210	01-Jan-2016	NULL	0	3
2	07-Jan-2015	Support	System Engineer	100	07-Jan-2015	NULL	0	1
2	04-Dec-2015	Support	Sr.System Engineer	110	07-Jan-2015	NULL	0	2
2	10-Oct-2016	Implementation	Sr.System Engineer	110	07-Jan-2015	NULL	0	3
2	25-Mar-2017	Implementation	System Analyst	120	07-Jan-2015	NULL	0	4
2	30-Nov-2017	Delivery	System Analyst	120	07-Jan-2015	NULL	0	5
2	01-Apr-2018	Delivery	Sr.System Analyst	130	07-Jan-2015	NULL	0	6
2	18-Jul-2018	Delivery	Sr.System Analyst	130	07-Jan-2015	18-Jul-2018	1	7

[v-shex-msft · Accepted solution]

Hi @gsrk1982 ,

I'm not so sure for your requirement, can you please explain more about these?

If You mean calculate available employee count in selected date range, you can try to use following measure formula:

Measure =
VAR _calendar =
    CALENDAR (
        MINX ( VALUES ( Date[Date] ), [Date] ),
        MAXX ( VALUES ( Date[Date] ), [Date] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( VALUES ( Table[Emp_ID] ) ),
        FILTER ( ALLSELECTED ( Tabele ), [Effective_from] IN _calendar )
    )

Regards,

Xiaoxin Sheng