cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jtlrlfrc
Frequent Visitor

Average Days between Purchases

Hi all,

 

I've scanned endless posts trying to find a solution to this, but the ones I've found don't quite adress what I'm looking for. For some reason this is the one thing that continues to stump me. If you have a solution or a link to a thread I missed it would be really appreciated!

 

Here's a very simplified dataset I'm working with:

 

CUSTOMER IDDATE
00701/10/2016
00101/09/2016
00601/09/2016
00301/08/2016
00101/06/2016
00501/04/2016
00401/02/2016
00301/02/2016
00201/01/2016
00101/01/2016

 

I'm trying to find a way to calculate how long each customer has been making purchases with us, which would be the time difference between the first and last time each Customer ID appears in the first column.

 

My more complicated issue is with trying to find the average time between purchases to see how long it takes on average for each customer to shop with us again. In the dataset above for example, Customer 001 takes an average of 4 days to repurchase. 

 

1 ACCEPTED SOLUTION

There is no need to do this via the advanced editor, all can be done through the UI. It's just that you might be able to study the steps if you past the code as it is into the advanced editor.

Have a look at the steps in this video:

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
ImkeF
Super User II
Super User II

You can do this in the query editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZczBDcAgDATBXu6NkM8QJ9Ri0X8b4F8sf0fadceLBkoXdhUadnPwkqxEVmkEfTW0RE/QTDSDtL4yaRDr/kf7AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"CUSTOMER ID" = _t, DATE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CUSTOMER ID", type text}, {"DATE", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CUSTOMER ID"}, {{"First", each List.Min([DATE]), type date}, {"Last", each List.Max([DATE]), type date}, {"All", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "AverageTimeBetween", each if Table.RowCount([All])>1 then Number.From([Last]-[First])/(Table.RowCount([All])-1) else ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"})
in
    #"Removed Columns"

Pls let me know if you need help implementing this code.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

jtlrlfrc
Frequent Visitor

Hi ImkeF, thanks so much for your help!

 

Are you aware of any way to do this in DAX? I don't have a strong enough background to adapt this code to my dataset in the advanced editor, I'm still very much a newbie.

There is no need to do this via the advanced editor, all can be done through the UI. It's just that you might be able to study the steps if you past the code as it is into the advanced editor.

Have a look at the steps in this video:

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

jtlrlfrc
Frequent Visitor

Hi Imke, that worked like a charm! In hindsight I made it much more complicated than it had to be, but I've been fiddling with PowerBI only a few days. Thanks so much for taking the time to explain it, I learned a little more today 🙂

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors