Dynamic Ageing
Hello, I am actually working on an AR ageing report. My data model is as follows: I have a AR transaction table with the amount, with a Date filed related to a calendar table. So far, I have managed to get the ageing as at today. I have added a calculated column, to calculate the age of the transaction and have used this measure to make it work: Ageing AR Amount = CALCULATE( PostAR[AR Net], FILTER( DISTINCT(PostAR[Ageing]), COUNTROWS(FILTER('Ageing Group', PostAR[Ageing] >= 'Ageing Group'[Min] && PostAR[Ageing] <= 'Ageing Group'[Max] ) ))) Ageing group table and ageing is as follows: So far so good. However, I need to have the calculated column age as dynamic, so that the ageing report is as at a selected date by the user. I have tried adding the ADDCOLUMNS to caluclated the age based on the date selected, but cannot figured it out.
Accounts Payable overdue
Hello, I am trying to solve one prblem with overdue and ageing. I can not solve that even in excel 🙂 Problem is that one vendor can have multiple invoices and multiple payments. There is no closing on Invoice row base but in a separate row which belongs to TYPE Payments. I need to calculate Total due for every vendor. And if it is possible to calculate ageing or overdue on basis 0-15, 16-30, >30. I tried hunderd times in excel to get some clue how it can be done, and to use that logic in Power Bi but without much success. I tried to create some measures, but no 🙂 Overdue = CALCULATE(SUM(BS_GK_[CREDIT])-SUM(BS_GK[DEBIT]),FILTER(BS_GK_,BS_GK[DUE_DATE]<TODAY())) Many Thanks for any kind of help. Here is table with one vendor and its invoice and payments and due dates. TYPE IDATE DUE_DATE ACCOUNT VENDOR_CODE VENDOR_NAME DOCUMENT DEBIT CREDIT BALANCE Invoice 01.01.21 05.03.21 4350 P-90059 PLATINUM A1 0,00 10.000,00 -10.000,00 Invoice 01.01.21 05.04.21 4350 P-90059 PLATINUM A2 0,00 12.000,00 -12.000,00 Invoice 01.01.21 10.04.21 4350 P-90059 PLATINUM A3 0,00 3.500,00 -3.500,00 Invoice 01.01.21 15.05.21 4350 P-90059 PLATINUM A4 0,00 5.000,00 -5.000,00 Invoice 01.01.21 25.05.21 4350 P-90059 PLATINUM A5 0,00 10.500,00 -10.500,00 Invoice 01.01.21 25.06.21 4350 P-90059 PLATINUM A6 0,00 2.500,00 -2.500,00 Invoice 01.01.21 27.06.21 4350 P-90059 PLATINUM A7 0,00 4.000,00 -4.000,00 Payment 05.03.21 4350 P-90059 PLATINUM Payment 1 20.000,00 0,00 20.000,00 Payment 20.04.21 4350 P-90059 PLATINUM Payment 2 10.000,00 0,00 10.000,00 Payment 31.05.21 4350 P-90059 PLATINUM Payment 3 5.000,00 0,00 5.000,00
