Dynamic reporting of accounts receivables ageing

Hello @M_K,

Thank You for posting your query onto the Forum.

The Total Invoices amount didn’t changed because there’s no active relationship between Dates table and the Accounts Receivable table due to multiple dates involved. Below is the screenshot provided for the reference.

In order to activate the relationship and find out the total invoices value by each of the respective dates you can try out the below formulas to activate the relationships by using the USERELATIONSHIP() function. Below are the formulas provided for the reference -

Total Invoices by Due Date = 
CALCULATE( [Total Invoices] , 
    USERELATIONSHIP( Dates[Date] , 'Accounts Receivable'[Due Date] ) ) 

Total Invoices by Invoice Date = 
CALCULATE( [Total Invoices],
    USERELATIONSHIP( Dates[Date], 'Accounts Receivable'[Invoice Date] ) )

Total Invoices by Settlement Date = 
CALCULATE( [Total Invoices],
    USERELATIONSHIP( Dates[Date], 'Accounts Receivable'[Settled Date] ) )

Now, you can see that that total invoices values changes dynamically as per the selection in the slicer. Below is the screenshot provided for the reference -

Also attaching the working of the PBIX file for the reference.

Lastly, I’m also providing few of the links of the videos created by @sam.mckay about how to handle multiple dates in Power BI.

Hoping you find this useful and meets your requirements. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Dynamic Accounts Receivable - 1.pbix (268.8 KB)

2 Likes