Forum Discussion

TheSweeper's avatar
TheSweeper
Icon for Helper I rankHelper I
3 years ago
Solved

Help 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 ...
  • TheSweeper's avatar
    3 years ago

    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 ChargesCALCULATE([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