Counting the occurence of active durations by month and into a graph

Question

Lets assume I currently have a spreadsheet that holds the dates of members at a crossfit gym and the duration of their memberships.

Date Started    Date Ended  Member Name
11/27/2019      12/19/2019  Aaron
10/21/2019      11/12/2019  Laura
10/21/2019      11/13/2019  Walt
10/9/2019       11/13/2019  Thomas
9/23/2019       10/23/2019  Kinsey
9/17/2019       10/1/2019   Ashley
8/29/2019       11/15/2019  Chris
8/27/2019       9/10/2019   Kevin

I need to create a Pivot Chart/Table or graph that counts the occurrences of members who had an active membership by month.

An example would be Chris would be considered an active member from August to November. So he would be included in August, September, October, & November.

Another example would be that Ashley was an active member from September and October. Therefore she would be included in September & October.

So far I've only been able to count the occurrences of members by their start date. Any ideas on how I would approach this?

Answer 1

You can set up a table which you can then graph.

You can do this with formulas where

• the first column is a list of the dates representing the first of each month
• the second column is a formula returning the counts in that month.

eg

Dates Column:

First Month:  I2: =EOMONTH(MIN(Table1[Date Started]),-1)+1
Subsequent Months:  =IFERROR(IF(EOMONTH(MAX(Table1[Date Ended]),0)>EDATE(I3,1),EDATE(I3,1),""),"")

Count Column:

=SUMPRODUCT((I3>Table1[Date Started]-DAY(Table1[Date Started]))*(I3<DATE(YEAR(Table1[Date Ended]),MONTH(Table1[Date Ended])+1,0)))

---

You can also do this using Power Query, which can have some advantages when you update, expand, or contract your data table.

PQ is availabe in Excel 2010 and later. In Excel 2016+, you access this from the Data tab --> Get&Transform from Table/Range after you've selected a cell in the table.

Except for entering the custom column formula, all of the steps can be done from the UI, and pasting the M-Code below into the advanced editor (possibly having to edit the Source Table Name) will reproduce the UI steps for your examination so as to understand what is being done.

Algorithm

• Add Custom Column which creates a List of the dates, by month, from Start to End
• Extract the values from the list with semi-colon delimiter
• Split the column into rows (using the semicolon delimiter)
  • Splitting into rows also effectively unpivots the columns
• Transform the column to the First of the month
• Group the date column with Operation = Count

M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Started", type date}, {"Date Ended", type date}, {"Name", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Months", each List.Transform({0..Date.Month([Date Ended])-Date.Month([Date Started])+(Date.Year([Date Ended])-Date.Year([Date Started]))*12}, (x) => Date.AddMonths([Date Started],x))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Months", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Extracted Values", {{"Months", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Months"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Months", type date}}),
    #"Calculated Start of Month" = Table.TransformColumns(#"Changed Type1",{{"Months", Date.StartOfMonth, type date}}),
    #"Grouped Rows" = Table.Group(#"Calculated Start of Month", {"Months"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Months", Order.Ascending}})
in
    #"Sorted Rows"