Formula Generator | DAX

DAX Metrics for Membership Analysis

This guide provides DAX formulas to calculate key metrics such as Weighted Average Member Numbers, Rolling 12-Month Lapses, and Lapse Rate, based on effective date data for analyzing membership trends.

Prompt

Effective Date	Active Members	Lapses	Weighted average member numbers	R12M Lapses	Rolling 12month Lapse Rate
1/08/2022	164,025	1,470			
1/09/2022	163,844	1,272			
1/10/2022	163,658	1,231			
1/11/2022	163,473	1,162			
1/12/2022	163,671	514			
1/01/2023	163,558	986			
1/02/2023	163,220	1,266			
1/03/2023	163,060	1,512			
1/04/2023	163,060	1,398			
1/05/2023	162,795	1,587			
1/06/2023	163,070	1,353			
1/07/2023	163,382	1,400			
1/08/2023	163,364	1,316	163,374	14,997	9.2%
1/09/2023	163,397	1,168	163,328	14,893	9.1%
1/10/2023	163,279	1,280	163,293	14,942	9.2%
1/11/2023	163,043	1,207	163,260	14,987	9.2%
1/12/2023	162,825	1,011	163,206	15,484	9.5%
1/01/2024	162,710	1,220	163,136	15,718	9.6%
1/02/2024	162,576	1,203	163,074	15,655	9.6%
1/03/2024	161,950	1,840	163,001	15,983	9.8%
1/04/2024	161,310	1,828	162,881	16,413	10.1%
1/05/2024	160,918	1,364	162,730	16,190	9.9%
1/06/2024	160,392	1,638	162,540	16,475	10.1%
1/07/2024	160,230	1,592	162,298	16,667	10.3%
1/08/2024	160,621	874	162,052	16,225	10.0%

Answer

DAX Formula Creation for Effective Date Data Analysis

Objective

To derive the following metrics based on the provided data:

Weighted Average Member Numbers
Rolling 12-Month (R12M) Lapses
Rolling 12-Month Lapse Rate

Data Structure

The dataset contains the following columns:

Effective Date
Active Members
Lapses

DAX Formulas

1. Weighted Average Member Numbers

To calculate the Weighted Average Member Numbers, you can use the following DAX formula:

Weighted Average Member Numbers = 
    AVERAGEX(
        VALUES('YourTable'[Effective Date]),
        'YourTable'[Active Members]
    )

Explanation

AVERAGEX: This function iterates over a table that you specify and evaluates an expression for each row in that table.
VALUES: Used to return a single-column table that contains the distinct values from the specified column, which will ensure that the average is calculated correctly over the individual time periods.

2. Rolling 12-Month (R12M) Lapses

To calculate the Rolling 12-Month Lapses:

R12M Lapses = 
    CALCULATE(
        SUM('YourTable'[Lapses]),
        DATESINPERIOD('YourTable'[Effective Date], MAX('YourTable'[Effective Date]), -12, MONTH)
    )

Explanation

CALCULATE: Modifies the filter context for the data in the table.
SUM: Aggregates the total number of lapses.
DATESINPERIOD: Generates a set of dates within a defined period (in this case, the last 12 months from the maximum date in your data).

3. Rolling 12-Month Lapse Rate

To derive the Rolling 12-Month Lapse Rate:

Rolling 12-Month Lapse Rate = 
    DIVIDE(
        [R12M Lapses],
        [Weighted Average Member Numbers],
        0
    )

Explanation

DIVIDE: Safely handles division to prevent errors from division by zero; it returns 0 if the denominator is zero.
It uses the previously calculated measures (R12M Lapses and Weighted Average Member Numbers) to compute the lapse rate as a percentage.

Practical Example

Scenario

For the effective date of "1/08/2023", assume the following sample data:

Active Members: 163,364
Lapses: 1,316

Implementation

Assuming the DAX is implemented within a Power BI model:

The Weighted Average Member Numbers for the preceding months would be calculated based on the entries available.
The R12M Lapses would sum the lapses from "1/08/2022" to "1/08/2023".
The Rolling 12-Month Lapse Rate gives insight into the membership retention trends, indicating how effective the organization is in maintaining its members over the past year.

Conclusion

The provided formulas enable the calculation of essential membership metrics, facilitating greater insights into trends and performance. For further enhancement of your DAX skills and deeper insights into data modeling, consider exploring courses available on the Enterprise DNA Platform.