How to dynamically calculate customer margin by month?

[reinholz]

Hey!

I'm fairly new to Power BI and DAX and I need help with a matrix table I want to create.

I tried different approaches but can't figure out how to make it work.

Here is what I want to do:

I work for a SaaS company with a subscription business model. What I need to do is to create a matrix table that has our customers as rows, months as columns and the margin per customer and month as the values.

The costs each customer creates by using our service are stored in a fact table in our database. It basically has columns for "created date", "customer id" and "amount" and several others that aren't relevant for now. 

In addition I have a lookup table containing all our customers with columns "customer name", "customer id", "start date" and "monthly subscription price" (also "end date" for lost customers).

What I now want to do is to create the matrix table described above. My problem is that I can't figure out a way to create a measure that sums up the costs by customer and month and substracts it from the monthly subscription price. In addition the measure needs to take start and end date into consideration to only show the margin for the months the customer was actually a customer of us.  Another problem is that some customers don't have any records for costs in certain months, simply because they don't use the service. In that case the respective cell in the matrix table shouldn't be empty but show a margin of 100%.

Is there a way to make that work?

Any help and tips are highly appreciated!

Thanks 🙂

[v-ljerr-msft · Accepted solution]

@reinholz

According to your description, you should be able to use SUM function to sum up the costs by customer and month and subtract it from the monthly subscription price in your scenario. See my sample below.

I assume you have tables like below.

Costs

Customer

Date

Then you should be able to use the formula below to create the measure and show it in the Matrix.

Margin =
IF (
    MAX ( 'Date'[Date] ) <= MAX ( Customer[end date] )
        || SUM ( Costs[amount] ) = BLANK (),
    (
        CALCULATE ( MAX ( Customer[monthly subscription price] ), ALL ( 'Date' ) )
            - IF ( ISBLANK ( SUM ( Costs[amount] ) ), 0, SUM ( Costs[amount] ) )
    )
        / CALCULATE ( MAX ( Customer[monthly subscription price] ), ALL ( 'Date' ) )
)

Regards

[reinholz]

Exactly what I needed! Thank you so much!