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

 

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • rajulshah's avatar
    rajulshah
    Resident 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
        Resident 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