Return the average of the difference of dates from a list (DAX)

[pfarias]

I need to return the average of the difference between a list of dates. Only one date may appear in this list, returning zero as the difference, but it may also appear two, or three, or more dates.

How could I do that using DAX?

06/01/2021

07/01/2021

15/01/2021

The expected result, based on this example, would be 4.5 days. Considering that the first day must be considered as BLANK and not as zero, so that the average is disregarded.

The proposal in using the measure that is being created is to return the average number of days of customer return, where each date is the purchase date.

I wrote a measure in which I got the expected result, but it performs very poorly.

AVERAGEX (
    SUMMARIZE ( fSales, [customerID], [dateSale], [storeID] ),
    VAR _LastDate =
        CALCULATE ( MAX ( fSales[dateSale] ), fSales[dateSale] < EARLIER ( [dateSale] ) )
    VAR _Diff =
        DATEDIFF ( _LastDate, [dateSale], DAY )
    RETURN
        _Diff
)

[amitchandak]

@pfarias , A new measure

AverageX( Table,
var _max = maxx(filter(Table, [Date] < earlier([Date]) ), [Date])
return
if(isblank(_max) , blank(), datediff(_max, [Date], [Day]))
)

[pfarias]

Thanks for your response. But it's doesn't work well.

I changed AverageX for AddColumns, to be able to visualize the result in the table.

customerID	saleDate	Measure
99988	12/06/2021	1
99988	12/07/2021	1
99988	12/15/2021	1