Amount of Open Orders on each day of the month

[RvdC]

I am not sure if this is possible in DAX but this is my question:

Therefore I created a simple test table with the name POWERBI_TEST_3:

ORDERNUMBER Unique. The Order starts on one date en had Ended on a date.

I want to know on each day in January 2019 (or more months) how many Orders have de status ‘Open’. The result should look like this, I therefore created a the table with the name DateJanuary with every date in January, a rownumber and a empty column for the numbers of open orders.

I created this SQL query to do the job.

In SQL I created a The Query to do the job look like:

DECLARE @COUNTER1 INT

DECLARE @NR_OPEN_ORDERS INT

DECLARE @DATJAN DATE

SET @COUNTER1 = 1

WHILE @COUNTER1 <= (SELECT COUNT(*) FROM Date_January)

BEGIN

SET @DATJAN = (SELECT Date_In_January FROM Date_January WHERE ROW_NMBR = @COUNTER1)

SET @NR_OPEN_ORDERS = (SELECT COUNT(DISTINCT ORDERNUMBER) AS AANTAL FROM POWERBI_TEST_3

WHERE (DATEDIFF(DAY, START_ORDER_DATE, @DATJAN) > 0

             AND DATEDIFF(DAY, @DATJAN, END_ORDER_DATE) > 0)

             OR

             (DATEDIFF(DAY, START_ORDER_DATE, END_ORDER_DATE) = 0

             AND START_ORDER_DATE = @DATJAN))

             UPDATE Date_January

             SET NR_OPEN_ORDERS = @NR_OPEN_ORDERS

             WHERE Date_In_January = @DATJAN

SET @COUNTER1 = @COUNTER1 + 1

END

The end result looks like:

In the SQL query I look at every record step by step per rownnumber. I don’t know if this is possible in DAX (or Power Query).

For some reason it’s not possible for me to use this SQL query. I am looking for a solution in PowerBI itself.

[Anonymous]

First off, this is the set-based T-SQL that does all of this at least an order of magnitude faster than the procedural code:

	UPDATE jan
	SET
		  jan.NR_OPEN_ORDERS = c.OrderCount

	FROM dbo.Date_January AS jan
	CROSS APPLY (
		SELECT
			count(*) AS OrderCount
		FROM dbo.PowerBI_test_3 AS pbi
		WHERE (
			pbi.Start_Order_Date <= jan.[Date_in_Jan]
			and
			jan.[Date_in_Jan] <= pbi.End_Order_Date
		)
	) AS c;

Secondly, if you have a PBI table with dates, say, Calendar, and want to know the number of open order on each day, you create a calculated column in Calendar with this formula:

[Open Orders] =
var __date = 'Calendar'[Date]
var __orderCount =
	COUNTROWS(
		filter(
			'Orders',
			'Orders'[start_order_date] <= __date
			&&
			__date <= 'Orders'[end_order_date]
		)
	)	
return
	0 + __orderCount

Best

D

[RvdC]

Many thanks for the query and DAX script. I was not familiar with APPLY operators. Great to know now. DAX query also runs fine.

Regards Rob

[BA_Pete]

Hi @RvdC ,

Try this measure:

_openOrders = 
VAR date_to_examine =
    MAX('calendar'[Date])
VAR number_orders =
    CALCULATE(
        DISTINCTCOUNT(orders[orderNumber]),
        KEEPFILTERS(date_to_examine > orders[startDate]),
        KEEPFILTERS(date_to_examine < orders[enddate])
    )
RETURN
    IF (ISBLANK(number_orders), 0, number_orders)

I get the following output:

*NB* The calendar table DOES NOT/MUST NOT have a relationship with the orders table.

Good luck

[Roberto_Campelo]

Hi!

I have just confirmed that the solution to all my problems is to not link the calendar table to the table I am getting the info about the open items from like you said before, but I don't understand why. Can you explain why is that the solution, please?

Thanks in advance

[BA_Pete]

Hi @Roberto_Campelo ,

This is 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.

For example, if there is a relationship between calendar[date] and orders[start date], and if the calendar date on the row is 01/01/2022, then this filters the order table to only rows where [start date] = 01/01/2022. This prevents us from being able to evaluate our measure across the full range of [start date] and [end date] values, as we need to.

There are a couple of ways around this other than using a disconnected date table:

- You can use the CROSSFILTER() function to 'switch off' the relationship (much like USERELATIONSHIP() 'switches on' relationships).

OR

- You can use functions like ALL(), ALLEXCEPT() etc. to reopen up all the values that have been filtered out by the relationship.

Pete

[Roberto_Campelo]

Hi Pete,

Thanks a lot for your explanation, it was really useful. Since I need to keep the relationship through the date, I used the CROSSFILTER() function and it worked perfectly. And now I understand how this row context works.

Roberto

[RvdC]

Thanks for the quick answer! I have tried the DAX script in Power BI on my Dummy dataset. It got only zeros as output in the added _openOrder2 column.

Maybe I overlooked something in the script?

_openOrders2 = VAR date_to_examine = MAX('Date_January_2'[Date_In_January])

VAR number_orders = CALCULATE( DISTINCTCOUNT(POWERBI_TEST_3[ORDERNUMBER]);

KEEPFILTERS(date_to_examine > POWERBI_TEST_3[START_ORDER_DATE]);

KEEPFILTERS(date_to_examine < POWERBI_TEST_3[END_ORDER_DATE])

)

RETURN IF (ISBLANK(number_orders); 0; number_orders)

Best,

RVDC

[BA_Pete]

@RvdC 

The DAX I provided was for a measure as stated, not a calculated column. Apologies if I've misunderstood your requirement here.

It does, however, provide the output you were looking for when applied to visualisations.

[Anonymous]

My code is for a calculated column. It's not a measure. If your code is for a calculated column, then it's wrong. If it's a measure, then... it's most likely wrong as well (because of relationships).

Advice: never use a function in DAX if you don't know how it works. CALCULATE does context transition, therefore your formula is almost certainly wrong.

Best
d