Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey,
I'm trying to count the number of order of a certain status till a certain date. The data is given in the table below.
OrderNumber | OrderStatus | Update date |
1 | 2 | 22-09 |
2 | 1 | 22-09 |
3 | 1 | 22-09 |
4 | 2 | 22-09 |
2 | 2 | 23-09 |
5 | 1 | 23-09 |
3 | 2 | 24-09 |
6 | 2 | 24-09 |
Here every day the order a new list of orders is added to the table. The released orders (1) that are finished (2) are also added to the table. I want to show the number of released over time and to know if this increases or decreases over time. The value at each day are given in this table.
Day | 22-09 | 23-09 | 24-09 |
Numberof Orders released | 2 (For order 2 and 3) | 2 (For order 3 and 5, order 2 changed to finished) | 1 (For order 5, order 3 changed to finished) |
I want to know how I can calculate the number of orders released.
Hi @Anonymous ,
We can create three columns and a measure to meet your requirement.
1. We need to create a date table that has no relationship with fact table.
2. Create a column in Table to get the closed date.
Close_date =
CALCULATE(
MIN('Table'[Update date]),
FILTER('Table','Table'[OrderNumber]=EARLIER('Table'[OrderNumber]) && 'Table'[OrderStatus]="closed"))
3. Create two columns to get the dates between open date and closed date.
Lowisnew =
CALCULATE(
COUNTROWS('Table'),
FILTER(
ALL('Table'),
'Table'[OrderNumber]=EARLIER('Table'[OrderNumber])
&& 'Table'[Update date]>EARLIER('Table'[Update date])))+1
New date =
VAR EXPIRE =
Calculate(
MIN('Table'[Update date]),
FILTER(ALL('Table'),
'Table'[OrderNumber]=EARLIER('Table'[OrderNumber])
&&'Table'[Lowisnew]=(EARLIER('Table'[Lowisnew])-1)))
RETURN
IF('Table'[OrderStatus]="open",IF(ISBLANK(EXPIRE), MAX('Dates'[Date]),EXPIRE-1),'Table'[Close_date])
4. At last we can create a measure to get the result.
Active =
CALCULATE (
COUNT( 'Table'[OrderNumber]) ,
FILTER (
'Table',
(
'Table'[Update date] <= MIN ( Dates[Date] )
&& 'Table'[New date] >= MAX ( Dates[Date] )
)
))
Then we can filter the open status.
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hey,
This does meet the requirements. I have one extra question. The order status is not a number, but it is a text with multipole abbreviations. I only need the first part of this status.
So the order status is as follows:
Order Status |
clsd cnf |
clsd prt |
teco cnf |
teco nmat |
Opn prt |
Opn nmat |
Is it than possible to only show the Opn orders?
I have sorted all these statuses in a different table where the status is either Closed, Teco or Open.
Hi @Anonymous ,
If you want to display the rows that contain “OPEN”, there are two ways you can refer.
1. In Power Query Editor, we can add a conditional column and filter it.
2. We can create a measure and put it in Filter on this visual, then configure it as 1.
Measure =
var _left = LEFT(MAX('Table (2)'[Order Status ]),1)
return
IF(
_left="O",1,0)
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
@Anonymous , Try a measure like
measure =
if( countx(filter(all(Table) , Table[Date] <=max(Table[Date]) && Table[OrderStatus]=2 && Table[OrderNumber] = max(Table[OrderNumber]) ), discount(Table[OrderNumber]))+0
- countx(filter(all(Table) , Table[Date] <=max(Table[Date]) && Table[OrderNumber] = max(Table[OrderNumber]) ), discount(Table[OrderNumber]))+0 = 0 , blank(), distinctcount(Table[OrderNumber]))
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |