I’ve developed a Power BI model containing an Invoice table. Customers can settle their bills through equal monthly instalments. The table includes a column indicating the number of approved instalments (monthly installment amount is total amount divided by the number of instalments). Now, I’d like to display the projected future income based on upcoming instalments due for collection. To achieve this, I’ve set up a Date table using the next DAX code:
Date =
VAR LastInvoiceDate = MAX('Invoices'[Datum racuna])
VAR MaxInstallment = MAX('Invoices'[Broj rata])
VAR MaxDate = EDATE(LastInvoiceDate, MaxInstallment - 1)
RETURN
CALENDAR(MIN('Invoices'[Datum racuna]), MaxDate)
Then I created new table named "Monthly Installments" using this code:
Monthly Installments =
GENERATE(
'Invoices',
ADDCOLUMNS(
GENERATESERIES(1, 'Invoices'[Broj rata]),
"InstallmentDueDate", EDATE('Invoices'[Datum racuna], [Value] - 1),
"MonthlyPayment", 'Invoices'[Invoice amount] / 'Invoices'[Broj rata]
)
)
Finally, I create a measure named "Total Future Income" in the "Monthly Installments" table:
Total Future Income (Cumulative) = SUMX('Monthly Installments', [MonthlyPayment])
I’ve received the expected future income as a cumulative amount. For instance, in January 2025, there’s an outstanding amount of EUR 1000 - based on due installments, followed by EUR 1500 due in February 2025, and EUR 2000 due in March 2025. The cumulative projected income looks like this:
January 2025: 1000 + 1500 + 2000
February 2025: 1500 + 2000
March 2025: 2000
However, I’d like my future income projection to display only the amounts due for the current month in the following format:
January 2025: 1000
February 2025: 1500
March 2025: 2000
I tried this measure, but still have cumulative amount as the result:
Total Future Income =
VAR SelectedMonth = SELECTEDVALUE('Date'[Date])
RETURN
SUMX(
FILTER('Monthly Installments', 'Monthly Installments'[InstallmentDueDate] = SelectedMonth),
'Monthly Installments'[MonthlyPayment]
)
Any idea how to calculate amounts due for each month separately?
If you want to use my Power BI model, you can download it from here (Google Drive link).
Best
