Help with Formula for Days in AR

[TheSweeper]

Hello!  Good Afternoon.  I am needing some help with a formula for showing casing the # of Days in AR.  Below you will find all the formulas I used inside of my report.  I also have provided several screenshots.  I am not sure what I am doing wrong.  Please help!!!!  My teammates are getting frustrated with me. 😞 I can't figure it out. 

Thank you so much in advance!

Sincerely,

Amanda 

Last 6 Month Charges = 1,689,901.39

How I got the Last 6 Month Charges is by using the Date (relative date) filter.

I also had a formula called IsLatest  ..that is a True / False statement set up to only sum charges by latest modified date so it wouldn't be adding up duplicate values.

Count of Days in Last 6 Months = 184

I used my Calendar Table and chose the column “Days in Month.” I then used the Date Filter to only count the days in the last 6 months. 

Average Charges in Last 6 Months = 8,966.86

I then set up a measure to calculate the Average Charges by dividing the Last 6 months charges by the # number of days in last 6 months.

I also applied the date filter for the last 6 months.

DAX FORMULA USED: Avg Charges = DIVIDE([Last 6 month Charges], COUNT(_Calendar[Days in Month]),0)

Total AR = 593,560.15

I just used my Charges Table and Chose "Balance"

Total AR Days = 66 ($ 59, 3560.15 Total AR / $ 8,966.86 Average Charge in last 6 months)

*****When setting up this formula I get the following value = 45

Using dax formula = 

AR Days = DIVIDE([Total Balance],[Avg Charges], 0)

[TheSweeper · Accepted solution]

Hello!  

Yes.  When you divide " $593,560.15" / "$ 8,966.86"  the answer is "66." In BI, when I using the below formula it gives me 45.  I am using the following formula:

AR Days = DIVIDE([Total Balance], [Avg Charges], 0)  *Using the following Filter Date: (Relative Date is in last 6 months)

Here are the other forumula I used:

1. Total Balance = SUM(Charges[Balance])

........Total Balance (which is also known as "Total AR")

2.  Last 6 month Charges= CALCULATE([Total Charges], DATESINPERIOD(_Calendar[Date], MAX(_Calendar[Date]), -184, DAY))  ....I didn't know how to go back 6 months so I just used "184" days.  I know sometimes it may not be 184 days but at least I got something in the meantime.  *Using the following Filter Date: (Relative Date is in last 6 months)

3.  Avg Charges = DIVIDE([Last 6 month Charges], COUNT(_Calendar[Days in Month]),0)

*Using the following Filter Date: (Relative Date is in last 6 months)

Hope this helps 

[djurecicK2]

Hi @TheSweeper ,

 Could you possibly post an example of the end result you are looking for?