Latest Enterprise DNA Initiatives

Days Between First and Last Purchase

I have a requirement to write a measure to find the difference in days (return an integer) between the first purchase and the last purchase made by a customer within a given time period.
Below are my thoughts but may be wrong:

VAR Firstpurchasedate = Date
VAR Nextpurchasedate = MIN(Date)
VAR DiffInDates = DATEDIFF(Firstpurchasedate, Nextpurchasedate) (where Nextpurchasedate >= Firstpurchasedate )

I know the above is not a DAX language but my thoughts.
How do I accomplish this, please?
Thank you

Days_Between 1st_and_Last_Purchase.pbix (221.1 KB)

Hi upwardD,

Good question. Find a possible solution attached.

One note: You ask the difference between first purchase and last purchase in the first part of question, but then ask the difference between first purchase and next purchase further down. I assumed you meant first and last purchase date, so that is what I implemented. Days_Between 1st_and_Last_Purchase.pbix (221.6 KB)

Hello @Rens, thank you for the quick response.
The requirement is actually the date difference between the first purchase and the very next purchase within the time frame. Sorry for the confusion.

Aha! See attachment now for the first and next!

Days_Between 1st_and_Last_Purchase.pbix (221.6 KB)

1 Like

Hi @Ren, I truly appreciate your help and swift response.
And I am sorry for not completely presenting the final cause of my expectation as it were. In my visualisation, I am to display MonthYear and a count of Date_Diff where Date_Diff is between 10 - 15
That is, when Date_Diff is between 10 and 15 then 1, else 0. My visualisation will be as in the attached image.
Thank you

Hi @Rens, do you know how I might accomplish the above task, please?
Thank you

Hi upwardD,

This is a completely different requirement. As your initial query was solved, I encourage you to (1) see how far you get with these building blocks and (2) post a new topic when stuck on a specific problem.

Kind regards,

1 Like