Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nkaruwo
Frequent Visitor

Accounts Receivable Aging Report

eHi All

 

I am a relatively new user in Power BI and am needing assistance with the following requirement.

My client is running on Dynamics AX 2012 R2 and would want to replicate The Customer aging Report in Power BI.

The client uses Transaction Date and NOT the Due Date for aging.

 

I am connecting to the clients transactions through SSAS which has the Customer Ageing Bands defined.

 

When they run their report like today the 26th December 2016, all open transactions for the period of December should be aggregated in a column called Current i.e. they looking for a calculation that sums up all amounts owing for transactions in the current month. 

 

-All open transactions in the Month of November  fall under column 30Days

-All open transactions in the Month of October  fall under column 60Days

-All open transactions in the Month of September  fall under column 90Days
-All open transactions in the Month of August  fall under column 120Days

-All open transactions prior to August fall under column +150

 

The client is also looking at a Date Slicer on the same report, whereby they can select a month in a year , say October 2016.  The aging should be  in such a way that all open transactions for October should fall under the column CURRENT, and the rest of the the aging  follows the pattern outline in the list above. 

 

Could someone urgently assist me on teh best way to approach this requirement.  I was thinking I could create some calculations based on The TOTALMTD for the CURRENT band Candidates using and also use the Aging Bands in the transactional file to group the same using the Aging Bands/Days.  I am really not sure how to get started on this requirement.

 

Thank you.

 

 

20 REPLIES 20
CheenuSing
Community Champion
Community Champion

Hi @nkaruwo

 

Please check out the link

https://www.sqlbi.com/articles/grouping-transactions-by-age-of-customers-in-dax/

 

If this still does not help let me .

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi @nkaruwo,

Based on your elaboration, I still not able to reproduce the scenario, couold you please post same samle data and expected result? Thanks a lot.

In addition, TOTALMTD Function evaluates the value of the expression for the month to date, in the current context. The date is 26th December 2016, when you select november, you count the open transactions during 26th December 2016-26th November 2016 or 1th November 2016-30th November 2016?

Best Regards,
Angelia

Hi Angela

Sorry for the late response.

I am still needing assistance with my issue.


The aging direction( Past Due, Not Due) and the Aging Bucket are being defined in a BI App called ZAP. But these are unusable as they are app does DATEDIFF function between the Date the report is being run and the Transaction Date of open AR transactions in Dynamics AX. Your Further assistance will be greatly appreciated.

 

I wanted to attach sample reports and data but could not find a way of doing so.


Regards

 

 

 

Hi @nkaruwo,

What do you mean of "The aging direction( Past Due, Not Due) and the Aging Bucket are being defined in a BI App called ZAP". You’d better load the .pbix file to one drive. And just pot the share link highlighted in yellow backgroud as follows. Thank you for understanding.

1.PNG

Best Regards,
Angelia

Hi Angela,

 

Find in link two documents, one with raw data and the other one a sample of the report .

https://1drv.ms/f/s!AqyZcatNfZNqaHeOB0gz9TrzS84

 

Regards

Hi @nkaruwo

 

Here is the link of PBIX and excel data file in One Drive

 

https://1drv.ms/f/s!ApP3mBZyGaHfgSIHGQl486ZNd6PY

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Can you provide the files both .pbx and excel files. I am getting an error when I am uploading the excel files, after getting out of querry mode, my both date and due date columns are having errors.

Hi, I cannot load it. Could I have this pbix?

Anonymous
Not applicable

Hi

 

I'm currently developing a similar report for my client too.  I'd be really grateful if you could post an up to date link to the PBIX please?

 

Kind regards

 

Hi @nkaruwo

 

I think I found a solution for you.

 

I assumed The Date.CalendarDate in the excel provided is the Due Date. Also I noticed there are lot of records with no value under this column. I removed all those rows. I removed all the columns except the CustomerCode, Date and Amount.

 

1. Loaded this excel data as ARData in the Power BI data model.

2. Created a Calendar table using the command

     Calendar = Calendar(Min(ARData[DueDate]),TOday())

3. Added Columns - Year, Month and YearMonth Columns

4. Created a measure under ARData table 

   WhatistheAge = INT ( MAX ( Calendar[Date] ) -  MAX ( ARData[DueDate]) ) 

   The is to create a generic measure that calculates the due date age.  The technique here is to take the  calendar date    

   displayed and then subtract the Duedate of ARData.  This gives the total what is the age of each transaction.

5. The final step is to create the different Age Measures as follows

 

6. Age 1 - 30 = CALCULATE ( Sum([Amount]),  FILTER ( ARData, [WhatistheAge] >= 1 && [WhatistheAge] < 31 ) )

 

7.Age  31 - 60 =  CALCULATE (     Sum([Amount]), FILTER ( ARData, [WhatistheAge] > 30 && [WhatistheAge] < 61 ) )

 

8. Age  61-90 = CALCULATE (Sum([Amount]), FILTER ( ARData, [WhatistheAge] > 60 && [WhatistheAge] < 91 ) )

 

9. Age  91-120 = CALCULATE ( Sum([Amount]), FILTER ( ARData, [WhatistheAge] > 90 && [WhatistheAge] < 121 ) )

 

10. Age 121 - 150 = CALCULATE (Sum([Amount]), FILTER ( ARData, [WhatistheAge] > 120 && [WhatistheAge] < 151 ) )

 

11. Age 151+ Days = CALCULATE (Sum([Amount]),FILTER ( ARData, [WhatistheAge] > 150 ) )

 

12. Current = CALCULATE (Sum([Amount]), FILTER ( ARData, [WhatistheAge] =  0) )

 

13.  NotYetDue = CALCULATE(Sum([Amount]),FILTER ( ARData, [WhatistheAge] < 0) )

 

14. Create measure Total = sum([Amount])

 

Now Plot the Table chart with CustomerCode, Total, and measures 6 to 13. 

 

Viola it works.

 

 

Sample screen shot Capture.GIF

 

Create a Slicer using Date from using Calendar Table.

 

If this works for you please accept this as solution and also give KUDOS.

 

Cheers

 

CheenuSing

 

 

     

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Am not able to use the Calendar function and then my DueDate and Date columns gives error when I click on quit and apply, and gets out of the query mode. Could you kindly provide your files or provide a solution for it.

CheenuSing
Community Champion
Community Champion

Hi @bjsp

 

Can you share the pbix in google drive or one drive and share the link  and the exact problem you are facing to find a solution.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi CheenuSing

 

Sorry for the delay , but i got started working on this today.  I am however getting ZEROs against this calculation, this is before I start bucketing the trransactions. Below is the DAX I am using.

AGE = INT ( MAX ( Calendar[Date] ) -  MAX ( CustTransOpen[TransDate]) ).

 

Am sure I am getting something terribley wrong or slightly , I am using TRansDate instead of the Due Date.

 

Regards

Hi @nkaruwo

 

It may be zero or any value depending on what is the max(Calendar[Date]) and MAX ( CustTransOpen[TransDate]).

Create a measure and individually check out.  

 

This will be so because there is no filter context applied.  Once you create a report with the various dimensions and age bucket values it will calculate according to each row.

 

Hope this helps.

 

Cheers

CheenuSing

 

  

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi CheenuSing

 

Thank you again for your valuable responses.  I have attached a link with the model I have created and a sample set of data.  Kindly consider the TRANSDATE , ACCOUNTNU, and AMOUNTCUR for testing.  The Model has all the calucations but in my report I only see the The Total and Current Colums yet the dates are different per transaction.

 

In your earlier explanation, I did not pick where I can be able to filter my report to a prior period, with the open  transactions of that period being aggregated in the Current Column? 

 

The organisation has defined accounting periods and probably the best explanation i should have given is that they want to run their aging by account period i.e. SUM up open transactions in a particular period and represent them in one column.  Depending on the Date Slicer, The date in context becomes teh Current and prior periods are represented according right up to where i have bucket 121-150 Days and thereafter sum up all the oldest ones into the 151 + Days column.  My modle should have the FiscalCalendar Periods table with STARTDATE and ENDDATE.

 

Below is the link to sample date and microsoft Powerbi   Desktop document.

 

https://jdsssa-my.sharepoint.com/personal/nehemiahk_just-dynamics_co_za/_layouts/15/guestaccess.aspx...

 

Regards

Hi @nkaruwo

 

My first observation is you should remove the relationship between the Calendar Table and CUSTTRANSOPEN (TRANSDATE).

 

You should be able to see the distribution by age.

 

As for the slicers what are slicers you are planning.  If it is only Calendar Date it should be Ok.  This is true irrespective of Calendar or Fiscal Year.

 

Check it out and let me know.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi CheenuSing

 

Thank you so much for your assistance, I removed the relatioship between the Date Table and the Customer Transaction Table and it is aging out correctly.  A good start.

 

So now , my other issues is how do i change this report to create and populate buckets based on the accounting periods or based on a fiscal or calendaer month that starts 01/01/2016 to 31/01/2016 etc.  Its basically an aging report based on Accounting Periods for an irganisation.

 

Regards

Hi @nkaruwo

 

Glad to note it worked for you.  From my understanding of Ageing Report it should be taken as at the end of a period, say Date, Monthend or YearEnd. 

 

Just change the total measure as

Total = calculate (SUm(ARData[AMOUNTCUR]), FILTER (CUSTTRANSOPEN, [TRANSDATE] <= max('Calendar'[Date]) ))

 

What this will do is consider only transactions between the first transaction till the period end date.

 

Say you have selected YearMonth = 201610.  The above expression will consider from the first date of transaction till 31/10/2016.

 

In such cases we should remove showing NotYetDue from your report.

 

I hope you have already created the FiscalYear and FiscalYearMonth Columns in your calendar table.

 

Try it out.

 

Cheers

 

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi CheenuSing

 

I will try this out and let you know.

 

Based on comment 'Say you have selected YearMonth = 201610.  The above expression will consider from the first date of transaction till 31/10/2016' if I select multiple YearMOnt 201610 201609 201608 201607 201606  will this dynamically categorise, not the startdate and enddate of each of the periods?

 

Regards

 

 

Hi @nkaruwo

 

I am not clear why you should select Multiple Year Month in a Ageing report.  It is always as at the end of the period.

 

Assuming even you select multiple year months, it will take end date of the max of year month.

 

if you select multiple YearMOnt 201610 201609 201608 201607 201606 , it will show as of 31/10/2016. 

The starting point will not be 01/06/2016 it will be from the First Transaction Date of your CUSTTRANSOPEN.

 

Hope I have made this clear.

 

If my understanding is nor right please share what is the scenario of period selection and what kind of output you expect. 

What transaction dates it should consider - from date and to date ?

 

Cheers

 

CheenuSing

 

 

 

 

g

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.