cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RvdC
Frequent Visitor

Amount of Open Orders on each day of the month

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:

 

PowerBI_Test_3.png

 

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:

 

Date_January.png

 

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.

6 REPLIES 6
Anonymous
Not applicable

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
Frequent Visitor

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
Super User II
Super User II

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:

openOrders.PNG

 

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

 

Good luck

RvdC
Frequent Visitor

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

@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
Not applicable

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors