Using DAX for a running sum based on dates

[bklyn3]

Hello 

I have a table with OrderID, Order_Date and Ship_Date. 

I am trying to add a column with the number of Open order (i.e. ordered but not yet ship) at the day a new order is placed. 
That is at the end of day  1/1/20 only 1 order is opened, On 1/2/20 two ordered are opened but on 1/3 only 1 remains open at the end of the day. 
The end goal is to plot hte number of open orders vs. Time. 

OrderID	Order_Date	Ship_Date	Open Order
1100	1/1/20	1/3/20	1
1102	1/2/20	1/3/20	2
1103	1/3/20	1/5/20	1
1104	1/4/20	1/7/20	3
1105	1/4/20	1/8/20	3

What would be the DAX function to create the "Open Order" Column. 

Thank you

[v-yalanwu-msft · Accepted solution]

Hi, @bklyn3 ;

You could create a measure,

Measure = CALCULATE(COUNT('Table'[OrderID]),FILTER(ALL('Table'),[Order_Date ]<=MAX('Table'[Order_Date ])&&[Ship_Date]>MAX('Table'[Order_Date ])))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.