Count number New and Lost Orders

[joshua1990]

Hello everyone!

I would like to calculate the number of new and lost orders per week based on a daily archive.

The daily archive has this structure:

Order Number	Date Archive	Article
100	01.07.2021	A
101	01.07.2021	B
102	01.07.2021	C
103	01.07.2021	D
104	01.07.2021	E
105	01.07.2021	F
106	01.07.2021	G
107	01.07.2021	H
108	01.07.2021	I
109	01.07.2021	J
100	02.07.2021	A
101	02.07.2021	B
102	02.07.2021	C
103	02.07.2021	D
104	02.07.2021	E
105	02.07.2021	F
106	02.07.2021	G
107	02.07.2021	H
108	02.07.2021	I

This is the structure if I pivot these numbers:

	WK26	WK27
	Do	Fr	Sa	So	Mo	Di	Mi	Do	Fr
Order ID	01.07.2021	02.07.2021	03.07.2021	04.07.2021	05.07.2021	06.07.2021	07.07.2021	08.07.2021	09.07.2021
100	1	1	1	1
101	1	1	1	1	1	1	1	1	1
102	1	1	1	1	1	1	1	1	1
103	1	1	1	1	1	1	1	1	1
104	1	1	1	1	1	1	1	1	1
105	1	1	1	1	1	1	1	1	1
106	1	1	1	1	1	1	1	1	1
107	1	1	1	1	1	1	1	1	1
108	1	1	1	1	1	1	1	1	1
109	1	1	1	1	1	1	1	1	1
110									1
Total	10	10	10	10	9	9	9	9	10

As you can see, there is a new order in the last column/ date as well as a order lost on the 05.07.2021.

In the end I would like to get a matrix in this format:

WK	# Orders	# New Orders	# Lost Orders
26	10	0	1
27	10	1	0

How would you do that using DAX?

I have added a sample file here:

https://easyupload.io/901pnv

[Greg_Deckler]

@joshua1990 Hmm, well, first of all I would not pivot your data. You can find new orders using DISTINCT and EXCEPT. Lost orders would essentially be the same. You would want to have a calculated column in your original table format that defined week number (WEEKNUMBER). If you have that, then maybe something like the following:

New Orders = 
  VAR __CurrentWeek = MAX('Table'[WeekNumber])
  VAR __LastWeek = __CurrentWeek - 1
  VAR __CurrentOrders = DISTINCT(SELECTCOLUMNS(FILTER('Table',[WeekNumber] = __CurrentWeek),"__Order",[Order]))
  VAR __LastWeekOrders = DISTINCT(SELECTCOLUMNS(FILTER('Table',[WeekNumber] = __LastWeek),"__Order",[Order]))
RETURN
  COUNTROWS(EXCEPT(__CurrentOrders,__LastWeekOrders))
  

[joshua1990]

@Greg_Deckler : Thanks, but using your approach just shows me how many distinct orders we have per week.

I would assume we need to take the first and last date per order into consideration to reflect new and lost orders, right?

[Greg_Deckler]

@joshua1990 Apologies, you will need some ALL statements in there:

New Orders = 
  VAR __CurrentWeek = MAX('Table'[WeekNumber])
  VAR __LastWeek = __CurrentWeek - 1
  VAR __CurrentOrders = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),[WeekNumber] = __CurrentWeek),"__Order",[Order]))
  VAR __LastWeekOrders = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),[WeekNumber] = __LastWeek),"__Order",[Order]))
RETURN
  COUNTROWS(EXCEPT(__CurrentOrders,__LastWeekOrders))