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)
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))
SET NR_OPEN_ORDERS = @NR_OPEN_ORDERS
WHERE Date_In_January = @DATJAN
SET @COUNTER1 = @COUNTER1 + 1
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.
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
Many thanks for the query and DAX script. I was not familiar with APPLY operators. Great to know now. DAX query also runs fine.
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.
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?
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.
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Mark your calendars and join us for our next Power BI Dev Camp!