Forum Discussion

apatwal's avatar
apatwal
Helper III
4 years ago
Solved

Transaction Gaps

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
DAX
DAX Commands and Tips

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    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