topic Re: Amount of open orders on each day in DAX Commands and Tips

Amount of open orders on each day

dicere — Thu, 09 Jan 2025 17:04:22 GMT

I'm trying to create a measure that calculates the number of orders that are open on each day of the month to place in a bar graph on my dashboard. I used the month of January as an example, but I need it to be for every day of the year.

I have the order number, the start date and the end date. Below I left an example of the table I have, the results I need and how the graph would look.

For example, the first order, Q5466805, I need it to count on days 6, 7, 8 and 9.

Re: Amount of open orders on each day

Sahir_Maharaj — Thu, 09 Jan 2025 17:05:20 GMT

Hello dicere,

Can you please try this approach:

Open Orders = VAR CurrentDate = SELECTEDVALUE('Calendar'[Date]) RETURN COUNTROWS( FILTER( 'Orders', 'Orders'[start_date] <= CurrentDate && 'Orders'[final_date] >= CurrentDate ) )

Re: Amount of open orders on each day

dicere — Fri, 10 Jan 2025 17:51:54 GMT

I think this measure is on the way for what I need, but it's still not calculating the correct number.
I have an active relationship between the calendar table and the orders table. I saw in another topic that there shouldn't be this active relationship between the tables because a relationship between the calendar and orders tables will force the orders table to be filtered by each calendar date due to row context.
I think this is affecting the measure and not calculating the correct number, but I can't disable this relationship as it will affect other measurements I already have.

In this topic it was mentioned that it would be possible to use CROSSFILTER() to deactivate this relationship, but I have doubts in which part of the measure I should add CROSSFILTER().

Basically in this measure I am using the “date” column from the calendar table, and the “start_date” and “end_date” columns from the orders table, but the active relationship is between the “date” column from the calendar table and the “activate_date” column from the table of orders.

Could you help me?

Re: Amount of open orders on each day

Sahir_Maharaj — Fri, 10 Jan 2025 19:32:08 GMT

Hello dicere,

Thanks for your reponse.

Can you please try this updated approach:

Open Orders = VAR CurrentDate = SELECTEDVALUE('Calendar'[Date]) RETURN CALCULATE( COUNTROWS( FILTER( 'Orders', 'Orders'[start_date] <= CurrentDate && 'Orders'[end_date] >= CurrentDate ) ), CROSSFILTER('Calendar'[Date], 'Orders'[activate_date], NONE) )

Re: Amount of open orders on each day

Anonymous — Wed, 15 Jan 2025 08:17:01 GMT

Hi, dicere

Based on your information, I create a table:

Create relationship:

Then create a new measure, It's like the dax expression described by Sahir_Maharaj

Open Orders = VAR SelectedDate = SELECTEDVALUE('Date'[Date]) RETURN CALCULATE( COUNTROWS( FILTER( 'Table', 'Table'[Start Date] <= SelectedDate && 'Table'[End Date] >= SelectedDate ) ), CROSSFILTER('Date'[Date], 'Table'[Start Date], NONE), CROSSFILTER('Date'[Date], 'Table'[End Date], NONE) )

Put then in column chart, here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.