Forum Discussion

HellRaizer2310's avatar
HellRaizer2310
Frequent Visitor
6 years ago

Custom calculation for Days Billings Outstanding

I have to do a calculation for Days Billing Outstanding and show the trend for the same over the months. DBO = (Cumilative Accounts Receivables at last day of Month/(Month's Billings+Prior Month′s B...
Need Help
Show and Tell
Tips and Tricks
rajulshah's avatar
rajulshah
Icon for Resident Rockstar rankResident Rockstar
6 years ago

Hello HellRaizer2310,

 

According to what I understood, I have developed the following DAX query. I hope it helps.

DBO = 
VAR PreviousMonthYear = (MONTH(TODAY())-1)&YEAR(TODAY())
VAR CurrentMonthYear = MONTH(TODAY())&YEAR(TODAY())
VAR LastDayofCurrentMonth = EOMONTH(TODAY(),0)
VAR NumberofDaysinLast2Months = COUNTROWS(DATESINPERIOD(Sheet1[Due Date],LastDayofCurrentMonth,-2,MONTH))
VAR CumilativeAccountsReceivables = CALCULATE(SUM(Sheet1[Accounts Receivables (USD)]),FILTER(Sheet1,CONCATENATE(MONTH(Sheet1[Due Date]),YEAR(Sheet1[Due Date]))=PreviousMonthYear))
VAR CurrentsMonthsBillings = CALCULATE(SUM(Sheet1[Original Invoice Amount (USD)]),FILTER(Sheet1,CONCATENATE(MONTH(Sheet1[Due Date]),YEAR(Sheet1[Due Date]))=CurrentMonthYear))
VAR PriorMonthsBillings = CALCULATE(SUM(Sheet1[Original Invoice Amount (USD)]),FILTER(Sheet1,CONCATENATE(MONTH(Sheet1[Due Date]),YEAR(Sheet1[Due Date]))=PreviousMonthYear))
RETURN DIVIDE(CumilativeAccountsReceivables,(CurrentsMonthsBillings+PriorMonthsBillings))*PriorMonthsBillings
HellRaizer2310's avatar
HellRaizer2310
Frequent Visitor
6 years ago

Hello rajulshah,

 

Thank you for the quick response, however the solution didn't work. It is providing cumilative AR instead of Days Billing Outstanding. So basically, the DBO is number of days the Total Accounts Receivables has been pending payment.

 

So the calculation would be as below:

VAR 1 (Cumilative sum of Accounts Receivables at the end of the current month)

VAR 2 (Sum of Original Invoice Amount at the end of current month)

VAR 3 (Sum of Original Invoice Amount at the end of prior month)

VAR 4 (Sum of number of days in current month and number of days in prior month)

 

Formula:

((VAR 1)/(VAR 2+VAR 3))*VAR 4

  • rajulshah's avatar
    rajulshah
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hello HellRaizer2310,

     

    You can try: 

    DBO = 
VAR PreviousMonthYear = (MONTH(TODAY())-1)&YEAR(TODAY())
VAR CurrentMonthYear = MONTH(TODAY())&YEAR(TODAY())
VAR LastDayofCurrentMonth = EOMONTH(TODAY(),0)
VAR NumberofDaysinLast2Months = COUNTROWS(DATESINPERIOD(Sheet1[Due Date],LastDayofCurrentMonth,-2,MONTH))
VAR CumilativeAccountsReceivables = CALCULATE(SUM(Sheet1[Accounts Receivables (USD)]),FILTER(Sheet1,CONCATENATE(MONTH(Sheet1[Due Date]),YEAR(Sheet1[Due Date]))=CurrentMonthYear))
VAR CurrentsMonthsBillings = CALCULATE(SUM(Sheet1[Original Invoice Amount (USD)]),FILTER(Sheet1,CONCATENATE(MONTH(Sheet1[Due Date]),YEAR(Sheet1[Due Date]))=CurrentMonthYear))
VAR PriorMonthsBillings = CALCULATE(SUM(Sheet1[Original Invoice Amount (USD)]),FILTER(Sheet1,CONCATENATE(MONTH(Sheet1[Due Date]),YEAR(Sheet1[Due Date]))=PreviousMonthYear))
RETURN DIVIDE(CumilativeAccountsReceivables,(CurrentsMonthsBillings+PriorMonthsBillings))*PriorMonthsBillings
    • HellRaizer2310's avatar
      HellRaizer2310
      Frequent Visitor
      6 years ago

      rajulshah even this solution didn't work. The issue is that the formula is looking at Today() for current & previous month however it should look at the posting date column for picking the current month(that is the month of the date in the current row of the posting date column) and same is the case with previous month. I tried customizing the formula by using MONTH & DATEADD syntaxes but neither worked. 

       

      Can anyone please help? We are supposed to go live tomorrow.