Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Number of order till a date

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. 

 

OrderNumberOrderStatusUpdate date 
1222-09
2122-09
3122-09
4222-09
2223-09
5123-09
3224-09
6224-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. 

 

Day22-0923-0924-09
Numberof Orders released2 (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. 

4 REPLIES 4
v-zhenbw-msft
Community Support
Community Support

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.

 

number1.jpg

 

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

 

number2.jpg

 

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

 

number3.jpg

 

Then we can filter the open status.

 

number4.jpg

 

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

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.

 

number1.jpg

 

number2.jpg

 

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)

 

number3.jpg

 

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.

amitchandak
Super User
Super User

@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]))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.