Total number of new orders

[Anonymous]

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:

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?

[dax · Accepted solution]

Hi @Anonymous , 

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.

[Fowmy · Accepted solution]

@Anonymous 

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

________________________

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

[Ashish_Mathur]

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.

[Anonymous]

@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

[Anonymous]

@Fowmy 

I have added the Order number in as an example for you. If this makes it easier. 

1st Table: Full Data

CL-2019/0090	GO-2019/0001	09/10/2019
CL-2019/0090	GO-2019/0002	13/02/2020
CL-2019/0091	GO-2019/0003	27/06/2019
CL-2019/0091	GO-2019/0004	23/09/2019
CL-2019/0092	GO-2019/0005	20/12/2019
CL-2019/0093	GO-2019/0006	26/09/2019
CL-2019/0094	GO-2019/0007	29/08/2019
CL-2019/0094	GO-2019/0008	27/11/2019
CL-2019/0094	GO-2019/0009	18/02/2020
CL-2019/0095	GO-2019/0010	05/09/2019
CL-2019/0095	GO-2019/0011	12/11/2019
CL-2019/0095	GO-2019/0012	26/06/2019
CL-2019/0096	GO-2019/0013	24/06/2019
CL-2019/0096	GO-2019/0014	25/07/2019
CL-2019/0097	GO-2019/0015	27/06/2019
CL-2019/0098	GO-2019/0016	05/12/2019
CL-2019/0099	GO-2019/0017	02/07/2019
CL-2019/0099	GO-2019/0018	21/10/2019

2nd table: Expected Results

CL-2019/0092	GO-2019/0005	20 Dec 2019
CL-2019/0093	GO-2019/0006	26 Sep 2019
CL-2019/0094	GO-2019/0009	18 Feb 2020
CL-2019/0098	GO-2019/0016	05 Dec 2019
CL-2019/0099	GO-2019/0018	21 Oct 2019

[Ashish_Mathur · Accepted solution]

Hi,

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

Hope this helps.

[dax · Accepted solution]

Hi @Anonymous , 

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.

[Fowmy]

@Anonymous 

Can you explain how did get the following two records from the sample source data you showed?

Part of your output

CL-2019/0098	GO-2019/0016	05 Dec 2019
CL-2019/0099	GO-2019/0018	21 Oct 2019

[Anonymous]

@Fowmy 

Both records match the criteria of:

• Have not had a prior order before 01/07/2019 or after 30/06/2020.

Therefor both records match the criteria of being a new order between 01/07/2019 and 30/06/2020.

CL-2019/0098  GO-2019/0016  05 Dec 2019 

CL-2019/0099  GO-2019/0018  21 Oct 2019

[Fowmy · Accepted solution]

@Anonymous 

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

________________________

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

[Fowmy]

@Anonymous 

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 

[Ashish_Mathur]

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?

[Anonymous]

@Ashish_Mathur 

My Mistake, sorry i shouldnt have included CL-2019/0095 in the example. 

You are right. 

Appreciate if you can help me figure this out. 

[Anonymous]

@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

[Fowmy]

@Anonymous 

Try This Measure:

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

[Anonymous]

@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?

[amitchandak]

@Anonymous ,

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

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

[Anonymous]

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