Transaction Gaps

[apatwal]

Hey,

I need help in creating below DAX.

We need to calculate the transaction gap between all transactions for each customer. The maximum transactions gap is the longest a customer has gone between transactions.

For this I have written below DAX, but the problem here is I am not able to find max and average value as its getting sum up.

Also, transaction Gap should exculde weekends.

Transaction Gap = 
var current_date = SELECTEDVALUE(data[Invoice Date])
var previous_invoice_date = 
CALCULATE(
    MAX(data[Invoice Date]),
    FILTER(
        ALLEXCEPT(data,data[Customer Name],data[Product]),
        data[Invoice Date] < current_date
    )
)

var diff = 
CALCULATE(
    COUNTROWS('Date'),
    DATESBETWEEN('Date'[Date],previous_invoice_date,current_date),
    'Date'[IsWeekend] = FALSE(),
    ALL(data)
)

RETURN diff

[CNENFRNL · Accepted solution]

For fun only, Excel worksheet formulas are powerful enough,

[johnt75]

You could create a measure like

Max gap = 
var summaryTable = ADDCOLUMNS( SUMMARIZE( 'data', 'data'[Customer Name], 'data'[Product]),
"@val", [Transaction Gap])
return MAXX( summaryTable, [@val])

You can do the same with AVERAGEX