Forum Discussion
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...
rajulshah
Resident Rockstar
Resident RockstarHello 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
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.