none
Distinct count between two dates

    Question

  • Hi,

    I have two tables,

    Table one is a date table.

    Table two has employee_id, hire_date and termination_date.

    I would like to calculate the amount of days that each employee worked and also if i go by date, i want to see how many employee the company has.

    I am trying to do a distinct count, but I am not sure what I am doing wrong. 

    =CALCULATE(DISTINCTCOUNT([employee]),FILTER(WorkDate,WorkDate[Date]>=[HireDate]&&WorkDate[Date]<=[TerminationDate]))

    This doesnt work, and I guess because between the hire date and the termination date he is one. 

    Any ideas? 

    Thanks

    Astrid

    Thursday, December 6, 2018 6:17 PM

All replies

  • Hi   astridaku,

     I am not very clear about your requirement, I think you could try to use DAX query like below to see whether it works or not

    Measure 9 = Calculate( DISTINCTCOUNT(employ[id]), FILTER(employ,employ[startd]<CALCULATE(MAX('DATE TABLE'[Date])) && employ[endd] >= CALCULATE (MIN('DATE TABLE'[Date] ))))

    and calculate datediff like below

    DATEDIFF = DATEDIFF(employ[startd],employ[endd],DAY)
    Create a Slicer based on Date table, then you will get the result like below


    Best Regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 7, 2018 2:24 AM