Count of Open Orders on specific date

[LowriDragon]

Hi!

I am trying to create a measure which will count all open orders on a given date (or avg for a date range).""


I have a date table connected to my AllOrders table through the 'Sales Order Date' column.

My table looks like this.

Sales Order	Sales Order Date	Invoice Date
75758	16/06/2024 0:00
75757	14/06/2024 0:00
75756	14/06/2024 0:00
75755	14/06/2024 0:00	14/06/2024 0:00
75754	13/06/2024 0:00
75753	12/06/2024 0:00
75752	12/06/2024 0:00
75751	12/06/2024 0:00
75750	12/06/2024 0:00	13/06/2024 0:00
75749	12/06/2024 0:00
75748	12/06/2024 0:00
75747	11/06/2024 0:00
75746	11/06/2024 0:00	11/06/2024 0:00
75745	11/06/2024 0:00
75744	11/06/2024 0:00	13/06/2024 0:00
75743	7/06/2024 0:00
75742	7/06/2024 0:00
75741	7/06/2024 0:00	13/06/2024 0:00

I need to count any row which doess not have an invoice date, but I also need to count rows where the specified date falls between the sales order date and the invoice date. 

I have tried the following DAX but it is not returning the value I expect.

Open Orders (Specific Date) =

VAR SelectedDate = MAX('Date'[Date])

RETURN

    CALCULATE(

        COUNTROWS('AllOrders'),

        FILTER(

            'AllOrders',

            'AllOrders'[Sales Order Date] <= SelectedDate &&

            ('AllOrders'[Invoice Date] >= SelectedDate || ISBLANK('AllOrders'[Invoice Date]))

        )

    )

Does anyone have any thoughts on how to achieve this?

Thanks in advance!

[ThxAlot · Accepted solution]

[LowriDragon]

The full data set is too big to upload. It goes back to 2012.

I am wanting it to return a total of the orders which were open on any specified day. So it would add the blanks to any where the specified date falls between the sale order date and the invoice date. It would also need to not count the currently open orders as the sales order date may be after the specified date.

[Irwan]

hello @LowriDragon 

alright, then from the table you provide in your first post, what result you want from that table?

Thank you.

[LowriDragon]

Ok, in the date I provided there are 18 rows. I want to select the date of 08 June 2024, this should return a count of 3 open orders.

If I was to select 14 June 2024 the result should be 13 open orders. This is due to some orders having been invoiced and more orders having been placed.

[Irwan]

hello @LowriDragon 

please check if this acommodate your need.

i assumed you need to countrow blank value in 'Invoice Date' column OR in between 'Sales Order Date' and 'Invoice Date', that means you want to add up those blank invoice date and in between value.

so the above DAX counts 16 rows as result of 13 rows for blank invoice date and 3 rows for in between value. Since i dont know the specified date you mentioned, i just put 12/6/2024 in 'Date' table just to show the calculated value (3 rows are less than 'Sales Order Date' and higher than 'Invoice Date').

Hope this will help you.

Thank you.

[LowriDragon]

Hi Irwan,

Thanks for taking the time to look at this.

I do want to count both the rows with blank value in the Invoice Date column, but I also need to count any row where the specified date falls between the sales order date and the invoice date. I have used the code you have in the image and it returns a value of 13 in the full data set. I know that I had 65 open orders on that day though so it is missing something somewhere.

[Irwan]

hello @LowriDragon 

when you said,

"I do want to count both the rows with blank value in the Invoice Date column, but I also need to count any row where the specified date falls between the sales order date and the invoice date.",

does it need to be separate result or as one result?

also can you share the whole data or is the data contained privacy? and please do tell what kind of result you need from the data you will provide.

thank you.