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 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 !

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

9 Replies

  • 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

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi

      Trying hard to get this to work.

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

       

      Regards, Henrik