I want to calculate Retention Amount and Count for a consolidated year data

[Newkidonblock22]

I need help to write the measure to calculate the retention amount and retention count for different year data, then find the variance between both years.

I know the calculation is (2024 revenue - 2024 new business amount/ 2023 new business amount)

[Anonymous]

Hi @Newkidonblock22 ,

I'm checking how the things are going on about this issue. Whether the answer from twi1 helps you?

If an answer has been helpful, please consider accepting the answer to help increase visibility of this question for other members of the Microsoft Q&A community.

[twi1]

Hi @Newkidonblock22 ,

I want to suggest you to define the DAX measures one by one

As assumptions lets say you have a table named RevenueData with columns:Year, TotalRevenue, NewBusinessAmount, Renewals, NewBusinessCount,
Total revenue by year

TotalRevenue2023 = CALCULATE(SUM(RevenueData[TotalRevenue]), RevenueData[Year] = 2023)
TotalRevenue2024 = CALCULATE(SUM(RevenueData[TotalRevenue]), RevenueData[Year] = 2024)
New Business Amount by Year:

NewBusinessAmount2023 = CALCULATE(SUM(RevenueData[NewBusinessAmount]), RevenueData[Year] = 2023)
NewBusinessAmount2024 = CALCULATE(SUM(RevenueData[NewBusinessAmount]), RevenueData[Year] = 2024)
Retention Amount:

RetentionAmount =
DIVIDE(
[TotalRevenue2024] - [NewBusinessAmount2024],
[NewBusinessAmount2023]
)
Renewals by Year:

Renewals2023 = CALCULATE(SUM(RevenueData[Renewals]), RevenueData[Year] = 2023)
Renewals2024 = CALCULATE(SUM(RevenueData[Renewals]), RevenueData[Year] = 2024)
New Business Count by Year:

NewBusinessCount2023 = CALCULATE(SUM(RevenueData[NewBusinessCount]), RevenueData[Year] = 2023)
NewBusinessCount2024 = CALCULATE(SUM(RevenueData[NewBusinessCount]), RevenueData[Year] = 2024)
Retention Count:

RetentionCount =
DIVIDE(
[Renewals2024] - [NewBusinessCount2024],
[NewBusinessCount2023]
)
Variance Between Years:

RevenueVariance = [TotalRevenue2024] - [TotalRevenue2023]
CountVariance = [Renewals2024] - [Renewals2023]
or the second way:

// Calculate Retention Amount for 2024
Retention Amount 2024 =
DIVIDE(
[2024 Revenue] - [2024 New Business Amount],
[2023 Total]
) * 100

// Calculate Retention Amount for 2023
Retention Amount 2023 =
DIVIDE(
[2023 Revenue] - [2023 New Business Amount],
[2022 Total]
) * 100

// Calculate Retention Count for 2024
Retention Count 2024 =
DIVIDE(
[2024 Renewal Count] - [2024 New Business Count],
[2023 Total Count]
) * 100

// Calculate Retention Count for 2023
Retention Count 2023 =
DIVIDE(
[2023 Renewal Count] - [2023 New Business Count],
[2022 Total Count]
) * 100

// Calculate Variance for Retention Amount
Amount Variance =
[Retention Amount 2024] - [Retention Amount 2023]

// Calculate Variance for Retention Count
Count Variance =
[Retention Count 2024] - [Retention Count 2023]

match column names with actual column names

Tell me if it was helpful or contact me for any further details
If you provide the actual data or more details on your data structure, I can help refine these calculations further.