Calculating Upgrade, Downgrade, New, and Canceled MRR For Each Month

[ddbaker]

Alright Power BI family, I've tried everything to make this work, and I need your help!

I'm creating a revenue report for a client, and I have been able to calculate their new, canceled, upgrade, and downgrade MRR, but I can't get the totals to add up correctly:

Here are all the relevant measures:

MRR = 
CALCULATE(
    SUM(Subscriptions[MRR Amount]),
    Subscriptions[Status] <> "canceled"
)

MRR (Current) = 
VAR t1 = 
    SUMMARIZE(
        Subscriptions,
        Customers[Customer ID],
        'Calendar'[Month Year],
        "MRR",
        _Measures[MRR]
    )
RETURN
SUMX(
    t1,
    [MRR]
)

MRR (Previous) = 
VAR CurrentMonth = SELECTEDVALUE('Calendar'[Month])
VAR CurrentYear = SELECTEDVALUE('Calendar'[Year])
VAR MaxMonthNum = CALCULATE(MAX('Calendar'[Month]), ALL('Calendar'))

RETURN
IF(
    HASONEVALUE('Calendar'[Month]),
    SUMX(
        FILTER(
            ALL('Calendar'),
            IF(
                CurrentMonth = 1,
                'Calendar'[Month] = MaxMonthNum && 'Calendar'[Year] = CurrentYear - 1,
                'Calendar'[Month] = CurrentMonth - 1 && 'Calendar'[Year] = CurrentYear
            )
        ),
        _Measures[MRR]
    ),
    BLANK()
)

MRR (Change) = 
_Measures[MRR (Current)] - _Measures[MRR (Previous)]

New MRR = 
VAR CustomerList = VALUES(Customers[Customer ID])
RETURN
SUMX(
    FILTER(
        CustomerList,
        _Measures[MRR (Previous)] = 0
    ),
    [MRR (Change)]
)

Churned Customer MRR = 
VAR CustomerList = VALUES(Customers[Customer ID])
RETURN
SUMX(
    FILTER(
        CustomerList,
        _Measures[MRR (Current)] = 0
    ),
    [MRR (Change)]
)

Upgrade MRR = 
VAR CustomerList = VALUES(Customers[Customer ID])
RETURN
SUMX(
    FILTER(
        CustomerList,
        _Measures[MRR (Previous)] < [MRR (Current)] && _Measures[MRR (Previous)] <> 0
    ),
    [MRR (Change)]
)

Downgrade MRR = 
VAR CustomerList = VALUES(Customers[Customer ID])
RETURN
SUMX(
    FILTER(
        CustomerList,
        _Measures[MRR (Previous)] > _Measures[MRR (Current)] && _Measures[MRR (Current)] <> 0
    ),
    [MRR (Change)]
)

I know that I need to include a Month Year column in my SUMX, but when I try the measure below, I get different values for the monthly totals (and I know the ones shown in my previous screenshot are correct).

Upgrade MRR = 
VAR t1 = 
SUMMARIZE(
        Subscriptions,
        Customers[Customer ID],
        'Calendar'[Month Year],
        "MRR Change",
        _Measures[MRR (Change)]
    )
RETURN
SUMX(
    FILTER(
        t1,
        _Measures[MRR (Previous)] < [MRR (Current)] && _Measures[MRR (Previous)] <> 0
    ),
    [MRR Change]
)

Also, here is a link to my PowerBI file. Thank you so much for your help with this!

[Ashish_Mathur]

Hi,

This measure seems to work

New MRR (Ashish) = SUMX('Calendar',[New MRR])

See last row in the table

[ddbaker]

@v-yiruan-msft Thank you for your response.

I tried your measure:

Churned Customer MRR = 
VAR CustomerList =
    VALUES(Customers[Customer ID])
VAR Dates = 
    VALUES('Calendar'[Month Year])
VAR ChurnedMRR =
    SUMX(
        FILTER(
            CustomerList,
            _Measures[MRR (Current)] = 0
        ),
        [MRR (Change)]
    )
RETURN
    SUMX(
        Dates,
        ChurnedMRR
    )

But the totals still don't work:

Any ideas?

[v-yiruan-msft · Accepted solution]

Hi @ddbaker ，

Please create a new measure as follows, instead of updating the original measure [Churned Customer MRR]. Later put this new measure in the matrix visual to replace the measure [Churned Customer MRR]...

New measure =
SUMX (
    VALUES ( Customers[Customer ID] ),
    SUMX ( VALUES ( 'Calendar'[Month Year] ), [Churned Customer MRR] )
)

If the total values are still incorrect, could you please provide me with a simplified pbix file in order to make troubleshooting and give a suitable solution? Thank you.

Best Regards

[amitchandak]

@ddbaker , refer if my customer retention logic can help you

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

[ddbaker]

@amitchandak I did get some ideas from your logic. I was able to fix the calcs for New, Upgrade, and Downgrade MRR, but the same logic didn't work for Churned MRR for some reason. Here's the measure I tried:

VAR t1 =
    SUMMARIZE(
        Subscriptions,
        Customers[Customer ID],
        'Calendar'[Month Year]
    )
RETURN
SUMX(
    t1,
    IF(_Measures[MRR (Current)] = 0, [MRR (Change)], 0)
)

This gives me an incorrect monthly result (as opposed to the measure in the original post) and doesn't sum the months. Which is strange because the same logic works for the other MRR measures. Do you have any ideas?

@mahoneypat  Feel free to chime in as well if you have any ideas.

[v-yiruan-msft]

Hi @ddbaker ,

Please try to create another new measure as below base on the original measure [Churned MRR] and put it on the visual to replace the measure [Churned MRR], later check whether it can get the correct total value.

New measure =
SUMX (
    VALUES ( Customers[Customer ID] ),
    SUMX ( VALUES ( 'Calendar'[Month Year] ), [Churned MRR] )
)

In addition, you can refer the method in the following links to resolve it.

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

Best Regards