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

[François]

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 period). The period being every month.

The result expected :

Jan 2018    -    18

Feb 2018   -     17

Mar 2018   -     18

Apr 2018   -     19

etc...

Not so obvious, I don't see how to create a measure for that. should I use a date table and an intermediate table to count for each different day ? it seems quite complicated, maybe there is an easier solution ? Thanks in advance !

[v-lili6-msft]

hi, @François

You could try to refer to this post:

https://community.powerbi.com/t5/Desktop/Track-the-Workload-by-counting-the-number-of-in-progress-ta...

Best Regards,

Lin

[richbenmintz · Accepted solution]

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

[Anonymous]

Below are measures I use for this type of thing. The first two are what you're asking for, and I included some additional helpful ones since you may be doing something similar.

Note that these measures require the calDate to be used in the visual or as a filter because there is no relationship between the calendar and customer tables.  

customerQty = COUNTROWS ( customers )

qtyActiveBeginningOfPeriod = CALCULATE ( customerQty , 
    FILTER ( customers ,
        startDate < min (calDate)
        && endDate > min (calDate))
)

qtyActiveEndOfPeriod = CALCULATE ( customerQty ,
    FILTER (customers ,
    endDate > max (calDate))
)

qtyRetainedBase = CALCULATE ( qtyActiveEndOfPeriod , 
    FILTER ( customers ,
        startDate < min (calDate))
)

qtyGrossNew = CALCULATE ( customerQty , 
    FILTER ( customers ,
        startDate >= min (calDate)
        && startDate <= max (calDate))
)

qtyNetNew = CALCULATE ( qtyGrossNew , 
    FILTER ( customers ,
        endDate > max (calDate))
)

 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.

[Anonymous]

Hi

Trying hard to get this to work.

Can you please show the DAX to build the summary table?

Regards, Henrik

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

Table = House

House1, Startdate = 01-01-2010, Enddate = 01-01-2020

House2, Startdate = 01-01-2010, Enddate = 01-01-2020

House3, Startdate = 01-01-1991, Enddate = 01-01-2007

Tabel = Person

Person 1, Moved out date = 01-05-2018

Person 2, Moved out date = 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

[Ashish_Mathur]

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]

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

[Stachu]

my guess is COUNTROWS with FILTER on your table with starts/ends dates - can you share how it looks?