I'm looking to create an MRR measure from my invoice lines table.
My invoice lines contain a start date, end date and amount. Invoices can be montly or annually so I calculate the duration of the subscription in months. The invoice amount is then multiplied with the daily exchange rate to get Euro value and then divided by the duration to get a normalized month amount.
I want to be able to sum the monthly revenue on any given date (in a table or line chart) as long as the date on the axis is within the subscription period on the invoice line.
Currently the result is very erractic because the Invoice table is linked to the Date table on Invoice[Created Date]. For instance on 14-02-2021 the result is 10, which is the sum of all invoices created on that date, eventhough many more are valid on that date.
What do I need to change in my DAX or data model to get the desired result?
Dax:
Data model:
Wrong result:
Helpful resources
Power BI Monthly Update - September 2025
Check out the September 2025 Power BI update to learn about new features.
