Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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.
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.
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
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
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.
Here's some sample data:
Call (visit) table:
Order table:
Calendar table:
CustomerID table:
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |