Custom calculation for Days Billings Outstanding

[HellRaizer2310]

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 Billings))×number of days in last 2 months.

I have daily data for last 2 years and would like to calculate the above metric in DAX and then create a visual showing the trend of the DBO.

Can someone please guide me in creating the custom DAX formula?

Below is the link to the sample data file.

Sample Data 

[rajulshah]

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]

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]

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]

@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.