cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kcantor Super Contributor
Super Contributor

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 Datanaut!




1 ACCEPTED SOLUTION

Accepted Solutions
kcantor Super Contributor
Super Contributor

Re: DAX Question : Average Days between purchases

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 Datanaut!




View solution in original post

3 REPLIES 3
Highlighted
kcantor Super Contributor
Super Contributor

Re: DAX Question : Average Days between purchases

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 Datanaut!




kcantor Super Contributor
Super Contributor

Re: DAX Question : Average Days between purchases

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 Datanaut!




View solution in original post

oristides Regular Visitor
Regular Visitor

Re: DAX Question : Average Days between purchases

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 64 members 2,534 guests
Please welcome our newest community members: