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
=COUNT({<order_status='Open'}>} DISTINCT Order_Ref_No)
ORDERS OPENED in X MONTH
=Count({<Date_Type={'Date Opened'}>} DISTINCT Order_Ref_No)
ORDERS CLOSED in X MONTH
=Count({<Date_Type={'Date Closed'}>} DISTINCT Order_Ref_No)
A simple bar chart with the dimension of
=IF(Key_Date>='01/01/2021', DATE(MONTHEND(Key_Date), 'MMM YYYY'))
and 2 measures of ORDERS OPENED and ORDERS CLOSED as per KPIs above
I would greatly appreciate insight as to how to solve this challenge. Many thanks for your help in advance!
