Calculating Overdue Balance Over Time In Single Matrix

[stuckplshelp]

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!

[daxer-almighty]

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-...

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.