Forum Discussion
Counting based on rolling time
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.
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.