Calculating Overdue Balance Over Time In Single Matrix

stuckplshelp — Fri, 16 Jul 2021 13:18:14 GMT

Hi All

Been stuck on this problem for a bit - would be amazing if anyone could point out any errors in my working so far or suggest a fix.

I essentially have a large customer ledger table (Aged Debt) with each line representing a posting on a customers ledger. Simplified version below;

Customer No

Document No

Posting Date

Due Date

Closed Date

Amount

I also have a Calender table as below:

Calendar = CALENDAR(MIN(Aged_Debt[Posting Date]),MAX(Aged_Debt[Posting Date]))

Month-Year = FORMAT('Calendar'[Date],"mmm yy")

I want to show a few things - the first being the Total Balance of the ledger at the end of the month which works fine using the following:

LastDate = MAX('Calendar'[Date])

Outstanding End of Month =

SUMX (

FILTER (Aged_Debt,

Aged_Debt[Posting Date] <= [LastDate]),

Aged_Debt[Amount])

I then pull Month-Year and Outstanding End of Month into a matrix which gives the correct figures for each month. However, I also need to show the Overdue Balance.

I've tried using the below but the figures aren't correct - they're well under what they should be. When I pull Customer No into the matrix to see what is returned for each Customer, a few of the balances are actually correct but the overwhelming majority are just zero.

Overdue End of Month =

SUMX(FILTER(Aged_Debt,

AND(OR(Aged_Debt[Closed Date]>[LastDate],ISBLANK(Aged_Debt[Closed Date])),

[LastDate]-Aged_Debt[Due Date]>=0)),

Aged_Debt[Amount])

My thinking with the above is that I need to calculate the total Amount, whilst filtering the data for anything which was either A) Closed off after the last date of each month or B) Has not yet been Closed. I also needed to see if each line was Overdue, hence the LastDate minus Due Date. If that is greater than zero it will be Overdue.

I then pulled Month-Year and Overdue End of Month into a matrix.

Any help would be greatly appreciated, thanks!

Re: Calculating Overdue Balance Over Time In Single Matrix

daxer-almighty — Sat, 17 Jul 2021 12:25:13 GMT

Looks like you won't get any answers as the problem formulation is not easily digestible. Please rephrase this so that it's clear and to the point. This might help: https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/td-p/1626726

Try to solve one thing at a time. Do not throw in many sub-problems into the same bag as this makes it harder for people to cook up quick solutions. Thanks.

topic Calculating Overdue Balance Over Time In Single Matrix in DAX Commands and Tips

Calculating Overdue Balance Over Time In Single Matrix

Re: Calculating Overdue Balance Over Time In Single Matrix