cancel
Showing results for
Did you mean:
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?

@Greg_Deckler

Any suggestions?

@greggyb

Proud to be a Datanaut!

1 ACCEPTED SOLUTION

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

Proud to be a Datanaut!

3 REPLIES 3
Highlighted
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.

Proud to be a Datanaut!

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

Proud to be a Datanaut!

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,

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 64 members 2,534 guests
Recent signins: