Forum Discussion

DynamicsHS's avatar
DynamicsHS
Icon for Helper II rankHelper II
4 years ago
Solved

Date table not showing all data within date range?

Hi, 

I have created a date table and connected it to an employee information table. When I filter the date range to the last 24 months I want the employee table to show terminated/inactive employees who have left the business. The table is currently showing that, but it is only showing 2 employee values when there should be around 7 employees who have left the business in the last 24 months. 

 

I have gone into the data and made sure all the dates are the correct data types. Does anyone know why the other terminated employees are not showing? 

 

Regards,

Henry 

Need Help
  • danextian's avatar
    danextian
    4 years ago

    hI DynamicsHS ,

     

    By end date you mean termination date? I am assuming the relationship between the dates table and end date is inactive. If so, you can't use this relationship unless you specify this in your measure by using USERELATIONSHIP.  Here's a sample formula:

    Terminated within = 
CALCULATE (
    COUNTROWS ( Employee ),
    USERELATIONSHIP ( 'Calendar'[Date], Employee[Termination Date] )
)

    There is a one-to-many single direction inactive relationship between Dates and Terminated Date

    Here's a sample screenshot of the output:

     

     

6 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    4 years ago

    Hi DynamicsHS ,

     

    How does your Dates table relate to your Facts table? What fields are included in the relationship. It is possible that your Dates table is filtering your Facts table by another date, not termination date. 

    • DynamicsHS's avatar
      DynamicsHS
      Icon for Helper II rankHelper II
      4 years ago

      Hello, 

       

      Thank you for the swift reply. 

       

      Currently the date table is connected to employees start date, end date, birth date, anniversary date on the employee fact table. 

       

      Regards,

      Henry

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        4 years ago

        hI DynamicsHS ,

         

        By end date you mean termination date? I am assuming the relationship between the dates table and end date is inactive. If so, you can't use this relationship unless you specify this in your measure by using USERELATIONSHIP.  Here's a sample formula:

        Terminated within = 
CALCULATE (
    COUNTROWS ( Employee ),
    USERELATIONSHIP ( 'Calendar'[Date], Employee[Termination Date] )
)

        There is a one-to-many single direction inactive relationship between Dates and Terminated Date

        Here's a sample screenshot of the output: