DAX Question : Average Days between purchases

[kcantor]

Time for the (now famous) middle of the week DAX phone a friend question! (Dates Styme me)

I have a sales table (Sales Performance) that uses line level records and contains customer numbers, Sales Order id, Invoice number, product id, order date, and salesperson id along with amounts and quantities ordered. My look up tables include Customer, Dimdate, Item, and SalesRep. If customers purchase more than one item, they have more than one line on the Sales Performance Table. If they have multiple purchases, they have more than one line on the Sales Performance Table differentiated by the SO# or the Invoice #. I need to create a calculation to determine if a customer has purchased before (which I think I have) and if they have purchased before, I need to calculate the average number of days between purchases.

Any help available today or are all of you all at the conference?

 @Seth_C_Bauer

@Greg_Deckler

Any suggestions?

@greggyb

[kcantor]

so, I have created two calculated columns. The first showing Date of First Purchase and the second showing Last Purchase Date. I have subtracted forming a third column and then created an if statement to flag customers with no further purchases or a difference of 0 days. Then I took an average using both AVERAGE and AVERAGEA.

A better method would be less expensive in terms of file size and performance.

All ideas are accepted.

[kcantor · Accepted solution]

My averages seemed a little high so I decided to use the Median instead. Unfortunately, My powerpivot did not support it as DAX so I created this little gem:

(MINX (FILTER ( VALUES ( Customer[Days between] ),
CALCULATE (
COUNT ( Customer[Days between] ), Customer[Days between] <= EARLIER ( Customer[Days between] ) )
> COUNT ( Customer[Days between] ) / 2 ), Customer[Days between])
+ MINX ( FILTER ( VALUES ( Customer[Days between] ),
CALCULATE (
COUNT ( Customer[Days between] ), Customer[Days between] <= EARLIER ( Customer[Days between] ))
> ( COUNT ( Customer[Days between] ) - 1 ) / 2 ), Customer[Days between])) / 2

I am posting this in hopes of helping someone else as I have now created my own very cumbersome answer.  Maybe a shorter answer will present it self but for now I feel like my microphone is off.

(tap tap tap)"Is this thing on?"

[oristides]

Hello I didnt underestand ho to get Average days beteewn purchases.

But I got other question, how do i get the standar deviation of the days between purchases.

I have:

Date table

Purchases table,

Customer tables.

Thanks,