cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Community Champion
Community Champion

DAX Question : Average Days between purchases

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




1 ACCEPTED SOLUTION
Community Champion
Community Champion

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?"





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Community Champion
Community Champion

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?"





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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,

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors