topic Help with Formula for Days in AR in DAX Commands and Tips

Help with Formula for Days in AR

TheSweeper — Wed, 16 Nov 2022 20:58:35 GMT

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)

Re: Help with Formula for Days in AR

djurecic — Wed, 16 Nov 2022 23:23:57 GMT

Hi TheSweeper ,

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

Re: Help with Formula for Days in AR

TheSweeper — Thu, 17 Nov 2022 15:20:05 GMT

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