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

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
dax
Community Support
Community Support

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.

View solution in original post

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

 

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

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
Ashish_Mathur
Super User
Super User

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

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

@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

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/
dax
Community Support
Community Support

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.

@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
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

@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

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

 

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group


@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 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

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

@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
Super User
Super User

@Anonymous 

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

@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

 

amitchandak
Super User
Super User

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

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

 

 

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.