I need to track historical values of over 90 days late invoices. I’ve attached a sample file with the expected results. Based on invoice due dates and payments, I had 2 invoices, 2 and 3, which were over 90 days late on April 1, 2021 and were not paid. Then by May 1, 2021, I had invoices 5,6 and 7 that were not paid and over 90 days old.
I’ve created measures to track historical monthly unpaid invoice amounts but need to create a measure to track just the over 90 days unpaid invoice amounts to match the bar chart I provided.
Hi @Usates - This is the same issue raised earlier but with better explanation. Still an example of Calculation would have been really helpful. Also, you mentioned 2 and 3 for April 1, 2021. This is again Incorrect, as those invoices shall be 4,5.
Anyways, I think I have got the requirement after spending good amount of time and the solution for same. Please try below. Output is matching with the count given.
PendingInvoices-Over90days =
VAR slicedByDate = min(DateTable[Date])
var PayTab = SUMMARIZE(filter(All(AR),AR[Type] = “Payment” && [date] <= slicedByDate),AR[Doc Num])
var InvTab = SUMMARIZE(filter(All(AR),AR[Type] = “Invoice” && [date] <= slicedByDate),AR[Doc Num])
var PendingInvoices = EXCEPT(InvTab,Paytab)
var PendingInvoices_Over90days = filter(all(AR),AR[Doc Num] in PendingInvoices && AR[Type] = “Invoice” && datediff(AR[Date],slicedByDate,DAY) > 90)
Hi @Usates, did the response provided by @ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!
