Counting Open Orders in a filter selected month with no key date in that month
Counting Open Orders in a given month with no key date in that month: Opened >= MonthEnd(Key_Date) and Closed <= MonthStart(Key_Date)
I am developing a performance reporting dashboard. A key requirement is to show the volume of work as it increases and decreases over time (usually in month periods). My challenge is: how can I show how many orders were open at any stage in X month? For this example, let's use "Oct 2021".
Currently, I can count all orders that have been Opened and/or Closed in Oct 2021 when the MonthYear Filter 'Oct 2021' is applied.
But that does not count, for example, an order Opened in Sept 2021 and Closed in Nov 2021. To show the volume of work in Oct 2021, I need to include all Orders that involved resources and activities in that month, even though they have no key date in the month of October 2021.
In other words, all orders with a 'Date Opened' before or on 31/10/21 (that's 10/31/21 for my American friends) AND all orders with a 'Date Closed' after or on 1/10/21 (US format = 10/01/21) OR'Date Closed' is NULL
This is what a currently have:
Various KPIs CURRENT ORDERS = current volume of OPEN ORDERS at THIS moment in time