DAX: filter a date column based on specific dates from another Table

Hello Anonymous ,

You would need primarily two tables for this: Employee table and a calendar table with all the date related fields( Year, month, qtr, etc. You do need to create a separate table for Year)

I have created a sample data table for this exercise:

Then you need to create a calendar table using DAX:

dtCalendar =
ADDCOLUMNS (
    CALENDAR (
        MIN ( dtEmpTable[DateOfJoining] ),
        MAX ( dtEmpTable[LastDateOfWorking] )
    ),
    "Year", YEAR ( [Date] )
)

Establish the relationships between these two tables on Date of Joining & Last working date (if applicable)

Then you may use following measures for the calculation

Joining Count =
CALCULATE (
    COUNTROWS ( dtEmpTable ),
    USERELATIONSHIP ( dtCalendar[Date], dtEmpTable[DateOfJoining] )
)


Inactive Count =
CALCULATE (
    COUNTROWS ( dtEmpTable ),
    USERELATIONSHIP ( dtCalendar[Date], dtEmpTable[LastDateOfWorking] ),
    dtEmpTable[Status] = "Inactive"
)


Inactive Count Overall = 
CALCULATE(
	[Inactive Count],
	FILTER(
		ALLSELECTED('dtCalendar'[Date]),
		ISONORAFTER('dtCalendar'[Date], MAX('dtCalendar'[Date]), DESC)
	)
)


Active Employee =
CALCULATE (
    [Joining Count],
    FILTER (
        ALLSELECTED ( 'dtCalendar'[Date] ),
        ISONORAFTER ( 'dtCalendar'[Date], MAX ( 'dtCalendar'[Date] ), DESC )
    )
) - [Inactive Count Overall]

Following is the output table:

You may find the solution file here

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/