Calculate the average active membership
The reason for my post is that I see no way to calculate the average active membership, for the members in my dataset. I hope you can help me to fix the problem.
Premis
I have created a very simple (fictive) dataset to define my challenge. The dataset contains 2 tables
Raw customer base
CustomerID | MembershipStart | MemberShipEnd | Definition | KPI | Numerical | REPORTDATE |
1 | 31-12-2021 | 31-1-2022 | Base | Beginning Of Period | 1 | 1-1-2022 |
2 | 5-1-2022 | 31-1-2023 | Base | Addition | 1 | 6-1-2022 |
3 | 3-1-2022 | 10-1-2022 | Base | Addition | 1 | 4-1-2022 |
4 | 12-1-2022 | 15-1-2022 | Base | Addition | 1 | 13-1-2022 |
4 | 12-1-2022 | 15-1-2022 | Base | Reduction | -1 | 16-1-2022 |
3 | 3-1-2022 | 10-1-2022 | Base | Reduction | -1 | 11-1-2022 |
1 | 31-12-2021 | 31-1-2022 | Base | Reduction | -1 | 1-2-2022 |
Calendar table:
DATE | MONTH | YEAR |
1-1-2022 | 1 | 2022 |
2-1-2022 | 1 | 2022 |
3-1-2022 | 1 | 2022 |
4-1-2022 | 1 | 2022 |
5-1-2022 | 1 | 2022 |
6-1-2022 | 1 | 2022 |
7-1-2022 | 1 | 2022 |
8-1-2022 | 1 | 2022 |
9-1-2022 | 1 | 2022 |
10-1-2022 | 1 | 2022 |
11-1-2022 | 1 | 2022 |
12-1-2022 | 1 | 2022 |
13-1-2022 | 1 | 2022 |
14-1-2022 | 1 | 2022 |
15-1-2022 | 1 | 2022 |
16-1-2022 | 1 | 2022 |
17-1-2022 | 1 | 2022 |
18-1-2022 | 1 | 2022 |
19-1-2022 | 1 | 2022 |
20-1-2022 | 1 | 2022 |
21-1-2022 | 1 | 2022 |
22-1-2022 | 1 | 2022 |
23-1-2022 | 1 | 2022 |
24-1-2022 | 1 | 2022 |
25-1-2022 | 1 | 2022 |
26-1-2022 | 1 | 2022 |
27-1-2022 | 1 | 2022 |
28-1-2022 | 1 | 2022 |
29-1-2022 | 1 | 2022 |
30-1-2022 | 1 | 2022 |
31-1-2022 | 1 | 2022 |
1-2-2022 | 2 | 2022 |
2-2-2022 | 2 | 2022 |
a PowerBI view for the relation between the tables
The challenge
I want to create a table/column where I can see what the average active days of membership is, per member and as a total of all the active members. The challenge is to determine the average membership duration over a chosen period.
Visualisation of what I’m trying to accomplish
Explanation
The raw customer base table contains: CustomerId, membershipStart, membershipEnd, definition, KPI, numerical and ReportDate.
The calendar is a date table.
The output we want to create in a measure is highlighted in green in the visual example.
Definition of measurements
Definition measurements
| Measure name | Definition |
| BOP | Beginning of period (any given period) with active customers |
| Addition | An additional active member |
| Reduction | A reduction of active members |
| EOP | End of period = BOP + ADD - RED = SUM of all numericals |
| Total membership days Cumulative | # of days all members have been active |
| AVG Active membership days EOP | # Of avg active days for active members in relation to the used reportdate based on the EOP membercount |
Additional documents
Helpful resources
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
