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!
