Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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:

DateLastDayDate
2019-07-012019-07-31
2019-08-012019-08-31
2019-09-012019-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?

 

Need Help
  • vivran22's avatar
    vivran22
    6 years ago

    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/

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

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

     

    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

  • vivran22's avatar
    vivran22
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        vivran22
        Community Champion
        6 years ago

        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/