Sum of Value between 2 Dates

[CoordAnalytics]

I've got two tables, Order and Calendar. I need to figure out how many business days between order sequence 1 and order sequence 2 for each order in the order table. There's a relationship between the two on change_date to cal_date and both are connected to a customer table based on customer ID. I was thinking there must be a way to write a dax measure to sum the membership_value between the date for sequence 1 and the date for sequence 2, but I can't figure out how to do that specifically for each order number. And each customer has their own calendars and they can vary by year. Any help would be appreciated. 

Order Table

Calendar Table

[mangaus1111 · Accepted solution]

Hi @CoordAnalytics ,

see my new pbi file. I hope now it is good.

https://1drv.ms/u/s!Aj45jbu0mDVJizDXMvR1_j8dnilA?e=x55QRg

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

[mangaus1111]

Hi @CoordAnalytics ,

try this measure

Business days between order sequence 1 and order sequence 2 =

INT(CALCULATE(MAX('Facts9'[Change Date]),'Facts9'[Order_Sequence]=2) - CALCULATE(MAX('Facts9'[Change Date]),'Facts9'[Order_Sequence]=1))

Here there is the pbi file

https://1drv.ms/u/s!Aj45jbu0mDVJizDXMvR1_j8dnilA?e=bpx2xY

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

[CoordAnalytics]

If I'm reading that correctly, that's going to count the number of days between the two, but I need the number of business days. For context, sequence 2 is supposed to occur within three business days of sequence 1. I need to know how many orders had sequence 2 occur more than 3 business days after sequence 1. For example, if sequence 1 happens on Friday and is sequence 2 happens on Tuesday, that still happened within 3 business days, but just finding the difference between the days would say 4 or 5 days. Does that make sense? That's why I'm getting to tripped up.