Well the course was just created from the educational point of view. And the original intent was to showcase how Ageing analysis can be done in Power BI. And once we understand the concept we can simply diversify our analysis as per the desired requirements.
Well yes, agreed that Due Date is within one month of the Invoice Date and that is the general scenario where businesses provide a certain credit period to their regular customers. So Due Date for the most of the businesses remain constant more often than not unless business organization follows the practice of providing different credit periods depending upon their relations and transactions with the respective customers.
In the given video, Sam had created the Table using the “Enter Data” option so once that table got created it cannot be modified. But you can also create the table using “New Table” option. As you mentioned above that -
“I’ve just realised that the due date is 30 days after the invoice date (in the Accounts Receivable table) - in which case the ageing will always sit under 0-60 days.”
You can create a dynamic table by writing the DAX using UNION( ROW() ) function where you can change the minimum and maximum period as per your requirements which is not possible if you just create a table using “Enter Data” option as mentioned above.
So below is the DAX code mentioned to create - “Dynamic Receivables Group”
Dynamic Receivables Group =
ROW( "Days Until Due" , "0 - 30 Days", "MIN" , 0 , "MAX" , 30 ) ,
ROW( "Days Until Due" , "31 - 60 Days" , "MIN" , 30 , "MAX" , 60 ) ,
ROW( "Days Until Due" , "61 - 120 Days" , "MIN" , 60 , "MAX" , 120 ) ,
ROW( "Days Until Due" , "121 - 240 Days" , "MIN" , 120 , "MAX" , 240 ) ,
ROW( "Days Until Due" , "241 - 360 Days" , "MIN" , 240 , "MAX" , 360 ) ,
ROW( "Days Until Due" , "360+ Days" , "MIN" , 360 , "MAX" , 100000 ) )
Once you’ve created this dynamic table, just replace the “Receivables Group Table” with the “Dynamic Receivables Group Table”. Below is the formula provided for the reference -
Receivables Groupings by Due Date 3 =
CALCULATE( [Invoices raised before selected date] ,
('Accounts Receivable'[Invoice Number] ) ,
'Dynamic Receivables Group' ,
[Days To Due Date] >='Dynamic Receivables Group'[MIN]
&&[Days To Due Date] <='Dynamic Receivables Group'[MAX]
) > 0
Now, you can observe that between 0 - 30 Days period also we’ve a value. Below is the screenshot provided for the reference -
I’m also attaching the PBIX file of the working for the reference.
Hoping this clears your doubt and meets your requirements.
Thanks and Warm Regards,
Attaching the revised file with proper formatting.
Dynamic Accounts Receivable - 3.pbix (271.7 KB)