cancel
Showing results for
Did you mean:
Highlighted
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:

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

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.

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

)``````

________________________

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 🙂

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
16 REPLIES 16
Highlighted
Super User IX

Re: Total number of new orders

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

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

Proud to be a Super User!

Highlighted
Community Champion

Re: Total number of new orders

@S229753

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 🙂

Highlighted
Helper I

Re: Total number of new orders

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

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
Highlighted
Helper I

Re: Total number of new orders

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?

Highlighted
Helper I

Re: Total number of new orders

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

Re: Total number of new orders

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

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
Highlighted
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 🙂

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