Goal: Generate a measure with Net Member Count by date. I need to provide net member count by month for the last three years.
Relevant tables:
- Members: This contains one row per member for every year they are a member. If a member joined in 2021 and is still a meber in 2024, they have four rows (one for year year). Columns include MemberID, Join Date, and Year.
- Date Table: Columns include (among others), Date and Year.
Relationships:
- 'Date Table'[Year] - 'Members'[Year] (Many to many, inactive)
- 'Date Table'[Date] - 'Members'[Join Date] (one to many, inactive)
Thought process: I've decided to break this measure into two parts and sum the results.
- Members who joined this year
- Members who are members during the year but joined in a previous year
My YTD New Members measure:
YTD New Members =
CALCULATE(
TOTALYTD(
DISTINCTCOUNT('Members'[MemberID]), 'Date Table'[Date]),
USERELATIONSHIP('Members'[Join Date], 'Date Table'[Date])
)
My members who are members that year but joined in a previous year table:
Members who joined before current year =
SUMMARIZE('Members',
'Members'[Year],
"Mem Count",
CALCULATE(
DISTINCTCOUNT('Members'[MemberID]),
YEAR('Members'[Join Date]) < 'Members'[Year]
)
)
The second part is where I start getting stuck. I have a table created using SUMMARIZE (see above) that provides the count of appropriate members, but I'm stuck on how to use that as a temp table in the measure and even on how to reference it in a measure if I create the table separately. If I create the table separately, then I still need to nest the column in some function, and using MAX() passes the max value, irrespective of the year.
This is the closest I've gotten, but I'm missing something (possibly obvious). Please help!
Net Members =
// New mems this year
CALCULATE(
TOTALYTD(DISTINCTCOUNT('Members'[MemberID]), 'Date Table'[Date]),
USERELATIONSHIP('Date Table'[Date], 'Members'[Join Date])
)
+
// Mems who joined in previous year but are still mems this year
CALCULATE(
MAX('Members who joined before current year'[Mem Count]),
USERELATIONSHIP('Members who joined before current year'[Year], 'Date Table'[Year])
)
The way this is written, it sums the correct year's new member count with the max number of non-new members (which happens to be this year). So the outcome is only correct for the current year.
