How to dynamically calculate customer margin by month?

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