Not-quite-semi-additive measures

xhead — Tue, 09 Jun 2020 20:40:17 GMT

I have a source table that has a snapshot of loan data for every day of the month (whether it changes or not, and no, I don't like this structure, but I'm constrained to use it for now). The sample I'm using is shown below (the actual table has many more columns, and some of them do actually change day by day, unlike what you see here).

Some of the columns in the table are "opening/closing balance" columns, like Principal Balance. This is a semi-additive measure, where I can add up the column across multiple loans, but I cannot add it over time - I must take the last snapshot.

Principal Balance = CALCULATE(sum('Loan Master'[principal_balance]),LASTNONBLANK('Loan Master'[report_dt], 'Loan Master'[report_dt]))

However, the Principal Paid and Interest Paid measures are only semi-additive *within the month*, but fully additive across months. I need to subtotal the column for the last day of *each month* within the filter context.

If I just use the same measure expression as the Principal Balance above, it works within the month.

Principal Paid = CALCULATE(sum('Loan Master'[principal_payment]),LASTNONBLANK('Loan Master'[report_dt],'Loan Master'[report_dt]))

But the totals over all months don't work (I didn't expect them to, as above):

In this case, I'd like the Total line to show $395.60 for Principal Paid and $3.50 for Interest Paid.

I can't seem to figure out how to use GROUPBY, SUMMARIZECOLUMNS, ADDCOLUMNS, SUMX or some combination to get it to work.

Anyone care to point me in the right direction?

Here is a sample PBIX.

Mike

Re: Not-quite-semi-additive measures

lbendlin — Tue, 09 Jun 2020 21:42:28 GMT

In my dates table I have "First Day of Month" and "Last Day of Month" flags. Might be worth considering?

Re: Not-quite-semi-additive measures

xhead — Mon, 15 Jun 2020 13:44:10 GMT

I fixed this myself.

I added a Date dimension table, related it to Loan Master by report_dt, then updated the measure expression to this:

Interest Payment - correct = SUMX ( SUMMARIZE ( 'Loan Master', 'Date'[Year Month] ), CALCULATE ( SUM ( 'Loan Master'[interest_payment] ), LASTNONBLANK ( 'Loan Master'[report_dt], 'Loan Master'[report_dt] ) ) )

Here's my updated PBIX file.

Mike

topic Re: Not-quite-semi-additive measures in DAX Commands and Tips

Not-quite-semi-additive measures

Re: Not-quite-semi-additive measures

Re: Not-quite-semi-additive measures