topic Re: Transaction Gaps in DAX Commands and Tips

Transaction Gaps

apatwal — Tue, 03 May 2022 12:25:26 GMT

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

Re: Transaction Gaps

johnt75 — Tue, 03 May 2022 12:46:52 GMT

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

Re: Transaction Gaps

CNENFRNL — Tue, 03 May 2022 13:28:42 GMT

For fun only, Excel worksheet formulas are powerful enough,