Counting based on rolling time

[erocco]

Hi!

I have 3 tables: 1 (Visits) with details when a customer was visited, 1 (Orders) with details of when & how much customers order, 1 calendar table linking them both by date (one to many, to date of visit, to date of order), one customer ID table linkinking both (one to many). 

I would like to create a measure where I can count if a customer was visited and created an order 7 days after the visit (or earlier).

I haven't been able to figure out what logic to use to approach this, so any help would be greatly appreciated! Most I was able to do was a distinct count of the customer ID in Visits filtering the Order table by Order Amount >0. But I have no idea how to do this in a rolling way and specific to the Visit Date.

I don't have easy access to the underlying dataset so can't manipulate it directly.

[Anonymous]

Does this help with your problem?

All customers place an order on the same date as visit, except for customer 666, who takes 1 year to place order after visit, so he is not counted.

[isabella]

Hi @erocco 

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards

Community Support Team _Isabella

[isabella · Accepted solution]

Hi @erocco 

You can do this in power query,I did a local test,you can take a reference,hope this helps.

1.First open the power query by click the Transform data button

1. then merge ordertable and visittable  by the same feild "CustomerFSFAID" ,choose the Merge Queries as New,so it would not affect the origin table,it is to create a new table.

Here I want to inform you that,I choose the left outer join,it means all the left table data will be reserved even if there is no match.

3.Then expand the visit table all column

4.Then create a custom column   to calculate days between the two dates

use expression ：=Duration.Days([CreatedDateID]-[CallDateID])

about how to calculate the date diff between two dates，please refer to this thread：https://www.pettakatechnologies.com/calculate-difference-between-dates-in-power-query/#:~:text=%20Ca...

now there is a column "Duration" in the merged table

5.Then we can filter the table base on this column

then the table has only 1 row left，I think the column CustomerFSFAID  is what you need.

At last ，you can press the left-top button “close & apply” to close the power query.

Best Regards,

Community Support Team _Isabella

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[erocco]

Here's some sample data:

Call (visit) table:

Order table:

Calendar table:

CustomerID table:

[ribisht17]

@erocco 

Please post some sample data to get quick answers

Regards,

Ritesh