Forum Discussion
Counting based on rolling time
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.
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
- 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=%20Calculate%20difference%20between%20dates%20in%20Power%20Query,%E2%80%93%20%E2%80%9CDURATION.TORECORD%E2%80%9D%20to%20calculate%20difference%20between...%20More%20?msclkid=8cc24fadd02f11ec921d1a14369a4d34
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.
5 Replies
Here's some sample data:
Call (visit) table:
Order table:
Calendar table:
CustomerID table:
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
- 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=%20Calculate%20difference%20between%20dates%20in%20Power%20Query,%E2%80%93%20%E2%80%9CDURATION.TORECORD%E2%80%9D%20to%20calculate%20difference%20between...%20More%20?msclkid=8cc24fadd02f11ec921d1a14369a4d34
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.
