How to find an average of Datediff in PowerBI

Question

I have set up a measure to calculate the day difference between starting date and end date, which excludes Public Holidays and Weekends. This gives me TAT per row in a table. I am now trying to calculate the average of the TAT, to be placed in a separate card. Note that some rows of the table do not have a completed date, and as such TAT is not identified, and therefore the average should not include these blanks.

I have tried to create a new measure that divides measure [TAT] by the number of [TAT], however the count function only works with columns. Have tried to create a quick measure, base value is [TAT], and catergory is [Date Finalised] (Which is a column). No luck. Below DAX codes I have tried.

TAT average per Date Finalised 2 = DIVIDE([TAT],COUNTROWS('Finalised'))

TAT average per Date Finalised = 
AVERAGEX(
    KEEPFILTERS(VALUES('Finalised'[Date Finalised])),
    CALCULATE([TAT])

This is how I worked out TAT
TAT = 
SUMX (
    FILTER (
        'Calendar',
        'Calendar'[Date] >= MIN ( 'Capture'[Date Captured] )
            && 'Calendar'[Date] <= MAX ( 'Finalised'[Date Finalised] ) && NOT( ISBLANK(MAX ( 'Capture'[Date Captured] )))
    ),
    'Calendar'[If work day])

The problem with the above functions is that they are not giving me the correct average. What my expected results are, in a table with 8 rows, but only 4 of them have an end date, with values of 29, 16, 16 and 12, the average would be (29+16+16+12)/4 which is 18.25. Instead I am getting average of 4.

Gangula Gangula · Accepted Answer · 2019-08-13T15:51:26

You can get your desired result using the following DAX Expression:

TAT average per Date Finalised =
CALCULATE (
    AVERAGE ( 'Finalised'[TAT] ),
    FILTER ( ALL ( 'Finalised' ), 'Finalised'[TAT] > 0 )
)

Tip: you can also add one more filter if you need like below

TAT average per Date Finalised =
CALCULATE (
    AVERAGE ( 'Finalised'[TAT] ),
    FILTER ( ALL ( 'Finalised' ), 'Finalised'[TAT] > 0 ),
    FILTER ( ALL ( 'Finalised' ), 'Finalised'[TAT] = 6 )
)

the above tip is just an example of how to use more filters. It doesn't make much sense, since you can simply use one filter.

How to find an average of Datediff in PowerBI

1 Answers