Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LowriDragon
New Member

Count of Open Orders on specific date

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 OrderSales Order DateInvoice Date
7575816/06/2024 0:00 
7575714/06/2024 0:00 
7575614/06/2024 0:00 
7575514/06/2024 0:0014/06/2024 0:00
7575413/06/2024 0:00 
7575312/06/2024 0:00 
7575212/06/2024 0:00 
7575112/06/2024 0:00 
7575012/06/2024 0:0013/06/2024 0:00
7574912/06/2024 0:00 
7574812/06/2024 0:00 
7574711/06/2024 0:00 
7574611/06/2024 0:0011/06/2024 0:00
7574511/06/2024 0:00 
7574411/06/2024 0:0013/06/2024 0:00
757437/06/2024 0:00 
757427/06/2024 0:00 
757417/06/2024 0:0013/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!
1 ACCEPTED SOLUTION
ThxAlot
Super User
Super User

ThxAlot_0-1718614098407.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

7 REPLIES 7
ThxAlot
Super User
Super User

ThxAlot_0-1718614098407.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



LowriDragon
New Member

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.

 

hello @LowriDragon 

 

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

 

Thank you.

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
Solution Supplier
Solution Supplier

hello @LowriDragon 

 

please check if this acommodate your need.

Irwan_0-1718591058218.png

 

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.

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.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors