The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
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:
User | Count |
---|---|
15 | |
12 | |
8 | |
7 | |
7 |
User | Count |
---|---|
24 | |
20 | |
12 | |
10 | |
7 |