Average Days Outstanding of ALL tickets as of certain date

Question

I am trying to figure out Dax for Average Days Outstanding taking into account all tickets that are open as of spec date to show trend over time. How many days tickets are outstanding on average over the period of time and how many days outstanding on tickets as of 8/25/2020? In this case, I am taking as of today's date but I need to be able to calculate as of 8/1/2020 as well.

Per example below, I have manually calculated in excel the outcome that I am looking for but do not know how to create DAX formula to reflect that. I did it in excel to show what outcome I am trying to achieve. In powerbi, I do have Calendar table with a date column and with relationship to ticket date.

As you can see, in my average formula I am taking into consideration all tickets that have current balance >0 as of 8/25/2020 and giving average counting prior rows ( prior tickets open as of 8/25/20). Currently, I know to how to calculate DAX days outstanding which is difference between ticket dates and today on tickets with current balance >0 but I do not know how to include in my DAX prior days where tickets are outstanding as well.

Your help is greatly appreciated!

mkRabbani mkRabbani · Accepted Answer · 2020-08-26T07:29:54

Try with the below logi-

running_average = 

VAR current_row_date = MIN(your_table_name[payment date])

RETURN
AVERAGEX (
    FILTER(
        ALL(your_table_name),
        your_table_name[payment date] <= current_row_date
    ),
    your_table_name[outstanding amount column name]
)

I can see some value in "payment date" column is blank. They will produce wrong results here. You need to somehow make adjustment for those BLANK values to get correct results.

Average Days Outstanding of ALL tickets as of certain date

1 Answers