Amount of open orders on each day

[dicere]

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.

[Anonymous · Accepted solution]

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.

[Sahir_Maharaj]

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
        )
    )

[dicere]

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?

[Sahir_Maharaj]

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)
    )