cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Total number of new orders

Hi,

I am trying to report on cases (Case Number) that have not had a prior order (Order Number) between a selectable date range (Order Start Date). 

I have the below table:

S229753_0-1595829615286.png

So the question i am trying to answer is:

How many new orders (cases that have not had a previous order) between 01/07/19 to 30/06/20?

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Total number of new orders

Hi @S229753 , 

If you want to hide the min order date which between 2019/7/1~2020/6/30, I think you could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Community Champion
Community Champion

Re: Total number of new orders

@S229753 

Use this measure to records that fall into your condition. I used a calendar table as well.

https://1drv.ms/u/s!AmoScH5srsIYgYJC4hWCUstIGpNaLw?e=nDQSXA

New Orders = 
VAR C = 
CALCULATETABLE(
    Table1,
    ALLEXCEPT(Table1,Table1[Case Number]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date]<MIN('Calendar'[Date]) ||
        'Calendar'[Date]>MAX('Calendar'[Date])
    )
)
RETURN
IF(
    ISBLANK(COUNTROWS(C)),
    CALCULATE(
    MAX(
        Table1[Order Date]),
        ALLEXCEPT(Table1,Table1[Case Number])
    )
 
)

 

Fowmy_0-1595923777393.png

 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

 

View solution in original post

Highlighted
Super User V
Super User V

Re: Total number of new orders

Hi,

CL-2019/0090 should also appear in the result.  You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

16 REPLIES 16
Highlighted
Super User IX
Super User IX

Re: Total number of new orders

@S229753 ,

firstnonblankvalue([Order start Date],max([Order Nimber]))

or
maxx(summarize(Table,table[Case Number], "_1",firstnonblankvalue([Order start Date],max([Order Nimber]))),[_1])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Champion
Community Champion

Re: Total number of new orders

@S229753 

Try This Measure:

Fowmy_0-1595832857608.png

 



New Orders = 
  
VAR CURR_ORDERS = VALUES(Table1[Ordr Number])
VAR ALL_ORDERS = 
    CALCULATETABLE(
        VALUES(Table1[Ordr Number]),
        FILTER(ALL(Table1),
            Table1[Case Number]<>MAX(Table1[Case Number])
        )
    )
VAR PAST_ORDERS = EXCEPT(CURR_ORDERS,ALL_ORDERS)    

RETURN
COUNTROWS(PAST_ORDERS)

 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

Highlighted
Helper I
Helper I

Re: Total number of new orders

@amitchandak 

 

Would this be a measure or a column?

 

It also still seems to be including order numbers where a previous order number already existed for that case number?

It needs to only count the order number if no previous order number existed for that same case number during the specified date.

 

 

Highlighted
Super User V
Super User V

Re: Total number of new orders

Hi,

Share a smaller dataset and show the exact result that you are expecting.  Share data in a format that can be pasted in an MS Excel workbook or share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper I
Helper I

Re: Total number of new orders

@Fowmy 

 

It is still showing Order Numbers that have previous existing order numbers within its Case number. 

 

EG: Case Number CL-2017/0001 has in total 3 Order Numbers. Fist Order numebr was created in 2017. The third one was created in 2019. Because CL-2017/0001 already had an existing Order Number in 2017, the 2019 Order number should not be included in the count. 

We only want to count the number of new Orders that have never had a previous Order created for its Client Number. 

I hape that makes sense now?

 

S229753_0-1595894624592.png

 

Highlighted
Helper I
Helper I

Re: Total number of new orders

@Ashish_Mathur 

See below example of full data with the expected results. 

 

Table 1: Full Data

CL-2019/0090  09/10/2019
CL-2019/0090  13/02/2020
CL-2019/0091  27/06/2019
CL-2019/0091  23/09/2019
CL-2019/0092  20/12/2019
CL-2019/0093  26/09/2019
CL-2019/0094  29/08/2019
CL-2019/0094  27/11/2019
CL-2019/0094  18/02/2020
CL-2019/0095  05/09/2019
CL-2019/0095  12/11/2019
CL-2019/0095  26/06/2019
CL-2019/0096  24/06/2019
CL-2019/0096  25/07/2019
CL-2019/0097  27/06/2019
CL-2019/0098  05/12/2019
CL-2019/0099  02/07/2019
CL-2019/0099  21/10/2019

 

Table 2: Expected Results

CL-2019/0092  20 Dec 2019
CL-2019/0093  26 Sep 2019
CL-2019/0094  18 Feb 2020
CL-2019/0095  12 Nov 2019
CL-2019/0098  05 Dec 2019
CL-2019/0099  21 Oct 2019

 

Highlighted
Helper I
Helper I

Re: Total number of new orders

@Ashish_Mathur 

See below. 1st table is the full data. 2nd table is the expected results. 

 

Question: "Cases that have not had a prior order between 1/7/2019 to 30/06/2020"

 

1st Table: Full Data

CL-2019/0090  09/10/2019
CL-2019/0090  13/02/2020
CL-2019/0091  27/06/2019
CL-2019/0091  23/09/2019
CL-2019/0092  20/12/2019
CL-2019/0093  26/09/2019
CL-2019/0094  29/08/2019
CL-2019/0094  27/11/2019
CL-2019/0094  18/02/2020
CL-2019/0095  05/09/2019
CL-2019/0095  12/11/2019
CL-2019/0095  26/06/2019
CL-2019/0096  24/06/2019
CL-2019/0096  25/07/2019
CL-2019/0097  27/06/2019
CL-2019/0098  05/12/2019
CL-2019/0099  02/07/2019
CL-2019/0099  21/10/2019

 

2nd table: Expected Results

CL-2019/0092  20 Dec 2019
CL-2019/0093  26 Sep 2019
CL-2019/0094  18 Feb 2020
CL-2019/0095  12 Nov 2019
CL-2019/0098  05 Dec 2019
CL-2019/0099  21 Oct 2019
Highlighted
Super User V
Super User V

Re: Total number of new orders

Hi,

CL-2019/0095 has a date of 26/06/2019 which is before the first date that you select in the slicer i.e. 1 July 2019.  So why should this appear in the result table?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Community Champion
Community Champion

Re: Total number of new orders


@S229753 

Order Numbers column is missing in your sample data. I assume your original request was based on order numbers and case number.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors