Hi,
I am trying to figure out a complex measure and I keep getting stuck. I have added an example table below named "Orders"
I need to create a column to show me how many phone calls (Activity) were made within 3 days of order placement (Order Date) based on each order (Order Number). The dates between the Order Date and Activty are listed in the Days Between/Order Activity column.
For example, on order "456", there are a total of 2 activities, one for the order placement and one for a phone call. I created the below calculation to show me how many total activities were completed for each order number.
Total Activities = VAR __CurrentRowOrder = Orders[Order Number] return COUNTROWS(FILTER(ALL(Orders), __CurrentRowClaim = Orders[Order Number]))
I want a column to show how many phone calls were made for each order number. For this example, I would have a column labeled "Phone Calls" and there would be "2" for order "123" and "1" for order "456".
I also need a column to show me how many phone calls were made 3 days or under after the "Order Date", essentially where Activity = "Phone Call" and Days Between Order/Activity <= 3 . For this example, order "123" would have "2" and order "456" would have "0" since the phone call was made 4 days after the order.
Is something like this possible?
| Order Number | Activity | Order Date | Activity Date | Days Between Order/Activity | Total Activities |
| 123 | Order Placed | 1/1/2021 | 1/1/2021 | 0 | 3 |
| 456 | Order Placed | 1/2/2021 | 1/2/2021 | 0 | 2 |
| 123 | Phone Call | 1/1/2021 | 1/3/2021 | 2 | 3 |
| 123 | Phone Call | 1/1/2021 | 1/4/2021 | 3 | 3 |
| 456 | Phone Call | 1/2/2021 | 1/6/2021 | 4 | 2 |
