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

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

I do not know how to compute Active houses - why does a figure of 2 appear for 2017?  You may download my PBI file from here.

Hope this helps.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thansk - but the essense of this thread is the calution of Active houses,using a start- and enddate.

I understand how to do that with the description from François.

 

My problem is the last calulation "Number of persons moved from houses as pct. of active houses, year-by-year"

 

I think I could figure it out, if I understood the last part of the message from François: "Usually after these measures, I build a summary table with SUMMARIZECOLUMNS by Year-Month, and I will relate that table to a calendar. Most of my visuals will be build on the summary table and another set of measures created against it" ... but I cant, alas.

 

Here it my case again:

HOUSES
Number  Startdate             Enddate
1                  01-01-2010      01-01-2020
2                  01-01-2010      01-01-2020
3                  01-01-1991      01-01-2007

 

PERSONS
Number MouvedOut
1                01-05-2018
2                01-05-2019

 

EXPECTED RESULT
Active houses:
2017   2018   2019
 2              2             2


Number of persons moved from houses:
2017   2018   2019
  0             1            1
 

Number of persons moved from houses as pct. of active houses, year-by-year
2017   2018   2019
 0      50     50