Repeating Recurring Revenues for a matrix style view with Start & end dates

[tyee]

I have data of our clients that has start and end dates, along with the revenue that is to be repeated within this timeframe (some customers have multiple transactions that could overlap). 

Many of the dates are also not the start or end of a month, but mid-month - and i'm also unsure of how to prevent double counting by summarizing by month/year where one client would have a start date of e.g. (7/5/2023 - 7/4/2024), and then have an identical contract for (7/5/2024 - 7/4/2025) for the same product (see below in red font).  I think i would need to allocate the balance of month payment on this product to July, but not sure how to do this so it displays accurately in the Matrix table.

Customer	MRR	Revenue Start	Revenue End	Product
174 Power Global	0	4/30/2023	4/30/2023	X
547 Energy	5500	5/16/2022	8/31/2023	X
Accelergen	2000	7/5/2023	7/4/2024	X
Accelergen	4465	7/5/2024	7/4/2025	X
Accelergen	3800	9/6/2022	9/6/2022	Y
Accelergen	3500	5/1/2023	12/31/2023	Z
Agilitas	4400	1/1/2022	12/31/2023	Y
Balanced Rock Power	4400	1/1/2022	12/31/2024	Y
Cypress Creek	5000	7/1/2021	12/31/2024	X
Energy Vault	5885	9/6/2022	8/31/2023	X
Idemitsu Renewables	5000	12/1/2022	5/31/2023	X
Indian Energy	4000	11/1/2022	10/31/2023	X
Lightsource BP	5500	2/1/2022	1/31/2024	X
Rondo	0	5/12/2023	5/12/2023	Y

Below is a table of what I'm trying to get to display in PowerBI - a matrix style view that i can run charts on

	1/1/2022	2/1/2022	3/1/2022	4/1/2022	5/1/2022	6/1/2022	7/1/2022	8/1/2022	9/1/2022	10/1/2022	11/1/2022	12/1/2022	1/1/2023	2/1/2023	3/1/2023	4/1/2023	5/1/2023	6/1/2023	7/1/2023	8/1/2023	9/1/2023	10/1/2023	11/1/2023	12/1/2023	1/1/2024	2/1/2024	3/1/2024	4/1/2024	5/1/2024	6/1/2024	7/1/2024	8/1/2024	9/1/2024	10/1/2024	11/1/2024	12/1/2024	1/1/2025	2/1/2025	3/1/2025	4/1/2025	5/1/2025	6/1/2025
174 Power Global	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
547 Energy	0	0	0	0	0	5500	5500	5500	5500	5500	5500	5500	5500	5500	5500	5500	5500	5500	5500	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Accelergen	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	3500	3500	3500	5500	5500	5500	5500	2000	2000	2000	2000	2000	2000	2000	0	4465	4465	4465	4465	4465	4465	4465	4465	4465	4465	4465
Rondo	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Agilitas	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Balanced Rock Power	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	4400	0	0	0	0	0	0	0
Cypress Creek	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000	0	0	0	0	0	0	0
Energy Vault	0	0	0	0	0	0	0	0	0	5885	5885	5885	5885	5885	5885	5885	5885	5885	5885	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Idemitsu Renewables	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

[Anonymous · Accepted solution]

Hi @tyee ,

You can create date table and add date column to slicer, then try formula like below to create measure:

Dates = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2025, 12, 31 ) ),
    "YearMonth", FORMAT ( [Date], "YYYY-MM" )
)

MonthlyRevenue = 
VAR SelectedDate = MAX('Dates'[Date])
RETURN
SUMX (
    FILTER (
        Clientes,
        Clientes[Revenue Start] <= SelectedDate &&
        Clientes[Revenue End] >= SelectedDate
    ),
    Clientes[MRR]
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.