Latest Enterprise DNA Initiatives

Days Between First and Last Purchase

DAX Calculations
#1

Hi
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)

#2

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)

MINIMUM Order Date
#3

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.

#4

Aha! See attachment now for the first and next!

Days_Between 1st_and_Last_Purchase.pbix (221.6 KB)

1 Like
#5

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

image
image802×641 13.4 KB

#6

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

#7

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,
Rens

1 Like