Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
@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))
@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?
@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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |