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

[Anonymous]

Hi all,

I am going nuts trying to find the solution to this problem. Hope the community might give a helping hand.

I have the following tables in my model:

(1) Table (Employees) which contains all employees with their hired dates (column name: hired_DT).

(2) Table (DateDimension) which is a DateDimension table from 01 January 2019 to 31 December 2019 (column name: date)

(3) Table (MthYrTXT) which has 2 columns, namely date and LastDayDate

MthYrTxt table looks as follows:

Date	LastDayDate
2019-07-01	2019-07-31
2019-08-01	2019-08-31
2019-09-01	2019-09-30

The relationships between these 3 tables are as follows:

Employees[hired_DT] -------------->DateDimension[date]

MthYrTxt[date]---------------------->DateDimension[date]

I need  a measure that will calculate the list of active employees for July 2019, August 2019 and September 2019.

The column "Date" of MthYrTxt table will be used as Rows in my Pivot Table.

Active employees for a particular month = all those which have a hired_DT <= the last day of that particular month.

In other words, Active employees for July 2019 will have this logic: where hired_DT <= "2019-07-31" and so on for the remaining months.

How can I build this Measure?

[vivran22]

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/

[Anonymous]

Thanks. You mention "You do need to create a separate table for Year". However, I can't find where you are making use of this table in your measures.

[vivran22 · Accepted solution]

@Anonymous 

This is in reference to the additional table you had mentioned in your orignal post:

(3) Table (MthYrTXT) which has 2 columns, namely date and LastDayDate

I have not used it in my calculation. These additional columns (Year, Month, QTR) can be used for filtering the visuals. For calculation, we just need Date column.

Hope this answers your query.

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/

[amitchandak]

Please refer to my article on the same line: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin