AR Aging Table Measures

Hi! Hoping you can be of help.

I’m trying to create the following AR Aging Measures based on a date selected by the user:

  • Current Due (0-30 days)
  • 31 - 60 days
  • 61 - 90 days
  • Overdue (over 90 days)

The issue is I have to get this data from two tables:

  • Payment History - refreshes daily and shows all collections received to date.
  • AR Outstanding - refreshes daily and shows all AR outstanding as at the date refreshed.

Hence for the tables refreshed Nov 2, 2018, if the user selects Oct 2nd, 2018 for example, then ideally the code should do the following:

  1. Filter “Payment History” table for all invoice date less than Oct 2nd and receipt date greater than Oct 2nd.
  2. Filter “AR Outstanding” table for all invoice dates less than Oct 2nd.
  3. Based on the due date, bucket the filtered lists into the measures above.

Could you help me figure out how to code this? The user-relationships make it a bit of a challenge. The long term goal is for the user to compare the AR aging at a particular period in time to the date refreshed for performance purposes (Ex: Oct 2nd Vs Nov 2nd). I know that may require two date tables but once I figure out this piece, the other piece shouldn’t be too hard to do.

.AR Aging example 1.pbix (189.8 KB)

You shouldn’t have to create two date tables to get the measure you would like. You can create an inactive relationship between the Fact-AR Outstanding & the Fact-Payment History tables. Then create a measure(s) that include USERELATIONSHIP.