Forum Discussion

François's avatar
François
Icon for Helper I rankHelper I
7 years ago
Solved

How to count all people active using start date / end date columns ?

Hello everybody !   We have a list of people with a start date & end date. I want to know how many people were active over time (meaning : start date < beginning of a period & end date > end of a ...
Need Help
  • richbenmintz's avatar
    richbenmintz
    7 years ago

    Hi François,

     

    i would think in your scenario you could use a disconnected date table as you would always be removing the filter context on the start date where you would likely be joined. 

     

    I following will work in that scenario

     

    Measure = Calculate(countrows('table'), 
    filter('table', 'table'[startdate]< min('date'[date]) && 'table'[enddate] > max('date'[date]))

    If you are able to provide a sample file or data, I could give you a more exact formula

richbenmintz's avatar
richbenmintz
Icon for Resident Rockstar rankResident Rockstar
7 years ago

Hi François,

 

i would think in your scenario you could use a disconnected date table as you would always be removing the filter context on the start date where you would likely be joined. 

 

I following will work in that scenario

 

Measure = Calculate(countrows('table'), 
filter('table', 'table'[startdate]< min('date'[date]) && 'table'[enddate] > max('date'[date]))

If you are able to provide a sample file or data, I could give you a more exact formula