Forum Discussion
Helper IHelp with Formula for Days in AR
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 =
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
2 Replies
- djurecic
Super User
Hi TheSweeper ,
Could you possibly post an example of the end result you are looking for?
- TheSweeper
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
