Showing only last balance

I am having a problem getting one cumulative sum for items that were paid for in each month when the only values I have are cumulative. In my record, I have the original balance and the current balance. While original balance does not change, the current balance changes after every payment. You will notice that sometimes more than one payment happens in a month.

I want only the final current balance value for each selected period (Month Quarter or Year).
I also want to use the current balance to subtract from the original balance in order to evaluate the total payments for each month, quarter or year.

My workout here returns wrong values for June and July where more than one payment date is involved

YTD Interest Test.pbix (55.4 KB) YTD Interest Test.xlsx (13.5 KB)

My Original balance formula works well and is not an issue when applied to any period context. The issue is when calculating the current balance and calculated periodic payments.

YTD Interest Test.pbix (56.2 KB)
YTD Interest Test.xlsx (16.0 KB)

I have finally gotten the DAX formula correct. My only problem is that the bottom line totals are not correctly reflecting what is on the table. They are only capturing the last value on the columns. Also I have tried to use the ALLNOBLANKROW and even variables to eliminate the row dates with no activity, but this is not working. Any help is appreciated.

@BenBen2,

Re: the bottom line total issue, check out this thread which addresses what sounds like the same problem, and references two great videos from @sam.mckay on this issue:

Hope this is helpful.

  • Brian

I already tried using Variables as in the examples given, but I am not getting the right results. If you want to have a go at it, I will appreciate. Please take a look at the code that I have on “Current balance”, which seems to work.YTD Interest Test.pbix (60.1 KB)

@BenBen2,

Sure – see example below using your Current Balance measure.

If you just drop Deposit Type and Current Balance into a Table visual, you’ll get this:

image

The Deposit Type rows work fine because they are defined in the initial data model, and the calculation is row by row, so IRA, Money and Share produce correct current balance results. However when Power BI gets to the Total row, it doesn’t have sufficient context to evaluate that row correctly, so it basically does this ¯\(ツ)/¯ and hands you a crazy number…

So you need some additional logic to force it to total the column vertically when it gets to the Total row. This is what the following measure does:

 Current Balance Virt Total = 

// Creates a virtual table from Deposit Types table also including the Current Balance measure
VAR VirtTable =
    ADDCOLUMNS(
        'Deposit Types',
        "CurrBal", [Current Balance]
    )
//Takes the sum of the CurrBal virtual column
VAR SumCurrBal =
    SUMX(
        VirtTable,
        [CurrBal]
    )

//Tests whether the row in question is a regular data row or a total row.  If the latter, uses the SummCurrBal variable
RETURN
IF(
    HASONEVALUE( 'Deposit Types'[Deposit Type] ),
    [Current Balance],
    SumCurrBal
)

Now when you drop Deposit Type and the new measure into the Table Visual, you get the correct total.

image

This is a really useful DAX pattern to keep in your toolbox, since the incorrect totals problem comes up all the time.

Hope this is helpful.

3 Likes

Thank you Brian. Your formula works well and I will have to review it for a better understanding. Thanks again for the insights…

Ben

@BenBen2,

Glad to hear that worked well for you. If you have any questions after reviewing it, just give a shout.

  • Brian