Number of Days between orders for a certain customer

[gbarr12345]

Hi,

I'm trying to create a measure that will calculate the number of days or weeks between Sales for a certain customer.

I'm struggling to get a working measure so any help would be greatly appreciated.

I've the link to sample Power BI data attached here too for your reference.

Many Thanks in advance!

Power BI File - https://drive.google.com/file/d/19ATFPApHUaFITZ9Yg1XmRxhbCS2uxifV/view?usp=sharing

Excel link - https://docs.google.com/spreadsheets/d/1kjrOwCWgMqvXCaDTpyFc8iJavVb4jtRR/edit?usp=drive_link&ouid=11...

My Drive - Google Drive

[lbendlin]

First step would be to check and adjust your data model.

Market Table is not a dimension

Item Table and Customer Table need single direction

What's the purpose of the Period table?

[gbarr12345]

Oh yes, I've updated this now thank you!

The period table was just a table created to put in the Financial Year etc but it's not being used here now.

The updated PBIX is here now - https://drive.google.com/file/d/19ATFPApHUaFITZ9Yg1XmRxhbCS2uxifV/view?usp=sharing

Excel - https://docs.google.com/spreadsheets/d/1FX1T2MmY7xv7rjvdH3IwahiqXupxjD3Y/edit?usp=drive_link&ouid=11...

[gbarr12345]

Hey, is there any solution you have been able to see from my data by any chance?

I've tried a few DAX measures but to no luck...

[lbendlin]

Why is Canada twice in your Market table?

[gbarr12345]

Apologies, that was put in twice in error. I can remove it now and let you know once removed.

[gbarr12345]

This has been updated now - https://drive.google.com/file/d/1ZOgSn00w6ZTbccryhUdEq6DhwN13UtRn/view?usp=drive_link

[lbendlin]

Here's a more appropriate version of the data model

But you can also opt not to connect the Calendar table.

Now your question  is 

calculate the number of days or weeks between Sales for a certain customer

That would require multiple transactions per customer.  Your sample data only lists each customer with a single transaction.

Please use the attached to improve your sample data.

In case of more than two transactions per customer do you want to see the individual differences, an average, or something else?

[gbarr12345]

I've followed your tips now and the differences seem a good bit off:

Some customers have more than 2 transactions as per the screenshot attached. I want to see the difference between each date for the customers. I have the customers in a slicer so only need to see the days between each transaction date.

[lbendlin]

I want to see the difference between each date for the customers.

How would that look like in a report?  I cannot visualize it.

[gbarr12345]

This is how it would look:

[lbendlin]

Where did the 130 come from?

[gbarr12345]

I'm not sure where the 130 came from...

The other figures were correct though.

[lbendlin]

please modify the sample PBIX to include a couple of customers with multiple transactions.

[gbarr12345]

I was actually in the process of doing that to try make things easier to understand:

PBIX - https://drive.google.com/file/d/1MwFy4oJuBzzrmwJ7MPCSJkuMkFMU-xxl/view?usp=drive_link

Excel - https://docs.google.com/spreadsheets/d/1QZMFURunOHq7vHtHVVYAMHFTsYBrzESV/edit?usp=drive_link&ouid=11...

Thank you.