topic Re: Amount of Open Orders on each day of the month in DAX Commands and Tips

Amount of Open Orders on each day of the month

RvdC — Fri, 27 Mar 2020 06:47:15 GMT

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)

(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.

Re: Amount of Open Orders on each day of the month

BA_Pete — Fri, 27 Mar 2020 11:02:52 GMT

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

Re: Amount of Open Orders on each day of the month

Anonymous — Fri, 27 Mar 2020 11:02:24 GMT

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

Re: Amount of Open Orders on each day of the month

RvdC — Sun, 29 Mar 2020 11:07:35 GMT

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

Re: Amount of Open Orders on each day of the month

RvdC — Sun, 29 Mar 2020 11:23:02 GMT

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

Re: Amount of Open Orders on each day of the month

Anonymous — Sun, 29 Mar 2020 11:36:03 GMT

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

Re: Amount of Open Orders on each day of the month

BA_Pete — Sun, 29 Mar 2020 14:00:08 GMT

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.

Re: Amount of Open Orders on each day of the month

Roberto_Campelo — Thu, 21 Apr 2022 12:51:12 GMT

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

Re: Amount of Open Orders on each day of the month

BA_Pete — Thu, 21 Apr 2022 13:32:12 GMT

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

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

Pete

Re: Amount of Open Orders on each day of the month

Roberto_Campelo — Fri, 22 Apr 2022 10:38:15 GMT

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

Re: Amount of Open Orders on each day of the month

dicere — Fri, 10 Jan 2025 12:07:19 GMT

Hello, I'm having difficulty knowing which part of the measure I should use the CROSSFILTER().
The calendar and orders table have an active relationship and I cannot deactivate it as it will disconfigure the other measures.
Can you help me?

Re: Amount of Open Orders on each day of the month

BA_Pete — Mon, 13 Jan 2025 06:50:49 GMT

Hi dicere ,

You would wrap the whole calculation in another CALCULATE and use CROSSFILTER there.

Based on the initial measure I provided, it would be something like this:

_openOrders = VAR date_to_examine = MAX('calendar'[Date]) VAR number_orders = CALCULATE( // New CALCULATE wrapper CALCULATE( DISTINCTCOUNT(orders[orderNumber]), KEEPFILTERS(date_to_examine > orders[startDate]), KEEPFILTERS(date_to_examine < orders[enddate]) ), CROSSFILTER(orders[orderNumber], 'calendar'[Date], none) // Remove crossfilter ) RETURN IF (ISBLANK(number_orders), 0, number_orders)

Pete