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.
Hi!
I really hope someone has a quick and good solution for my issue.
What I need to do is to calculate how many order_type "order" that are generated after the customer placed the order_type "ht_order". Moreover, the "order" should be placed within 30 days after the "ht_order" was placed.
For example, customer 123 placed a "ht_order" 01-01-20 and then another "order" 20-01-20 (19 days later) so this should be counted.
Expected outcome from below tables are 6 orders and sales of 830.
Example data:
Sales Table
customer_key | order_number | order_type | sales | order_date |
123 | 1001 | ht_order | 10 | 01-jan-20 |
123 | 1020 | order | 150 | 20-jan-20 |
124 | 1100 | ht_order | 10 | 10-okt-19 |
124 | 1200 | order | 140 | 10-dec-19 |
125 | 1201 | ht_order | 10 | 11-jan-20 |
125 | 1202 | order | 130 | 15-jan-20 |
126 | 1205 | order | 110 | 02-feb-20 |
127 | 1206 | ht_order | 10 | 10-mar-20 |
128 | 1207 | ht_order | 10 | 17-maj-20 |
129 | 1208 | ht_order | 10 | 10-jun-19 |
129 | 1209 | order | 180 | 22-jun-19 |
130 | 1210 | ht_order | 10 | 04-jun-20 |
130 | 1211 | order | 120 | 13-jun-20 |
130 | 1212 | order | 150 | 13-sep-20 |
131 | 1213 | ht_order | 10 | 15-mar-20 |
131 | 1005 | order | 130 | 15-maj-20 |
132 | 1215 | ht_order | 10 | 01-aug-20 |
132 | 1220 | order | 120 | 05-aug-20 |
132 | 1225 | order | 130 | 08-aug-20 |
Customer Table
CUSTOMER_KEY | CUSTOMER_EMAIL |
123 | 123@gmail.com |
124 | 124@gmail.com |
125 | 125@gmail.com |
126 | 126@gmail.com |
127 | 127@gmail.com |
128 | 128@gmail.com |
129 | 129@gmail.com |
130 | 130@gmail.com |
131 | 131@gmail.com |
132 | 132@gmail.com |
Big thanks in advance!
Solved! Go to Solution.
@Anonymous,
Try this. I revised the logic to work at both the total level and the year/month level.
Count of Order =
SUMX (
ALL ( Customer ),
VAR vCustomer = Customer[customer_key]
VAR vHTOrderRow =
FILTER (
ALL ( Sales ),
Sales[customer_key] = vCustomer
&& Sales[order_type] = "ht_order"
)
VAR vHTOrderDate =
MAXX ( vHTOrderRow, Sales[date_key] )
VAR vCountOrderRows =
CALCULATE (
COUNTROWS ( Sales ),
Sales[customer_key] = vCustomer,
Sales[order_type] = "order",
Sales[date_key] <= vHTOrderDate + 30
)
RETURN
vCountOrderRows
)
Proud to be a Super User!
@Anonymous,
The measure below assumes one "ht_order" per customer (per the sample data). If this is not the case, let me know.
Count of Order =
SUMX (
ALL ( Customer ),
VAR vCustomer = Customer[customer_key]
VAR vHTOrderRow =
FILTER (
ALL ( Sales ),
Sales[customer_key] = vCustomer
&& Sales[order_type] = "ht_order"
)
VAR vHTOrderDate =
MAXX ( vHTOrderRow, Sales[order_date] )
VAR vOrderRows =
FILTER (
ALL ( Sales ),
Sales[customer_key] = vCustomer
&& Sales[order_type] = "order"
&& Sales[order_date] <= vHTOrderDate + 30
)
RETURN
COUNTROWS ( vOrderRows )
)
Proud to be a Super User!
Thanks for the solution and great reply!
@DataInsightswould it be possible to tweak the formula to be based on 3 tables instead of only 2 (realized that I have the date in a separately table). In that case, this is the example data:
Sales table
customer_key | date_key | order_number | order_type | sales |
123 | 20200101 | 1001 | ht_order | 10 |
123 | 20200120 | 1020 | order | 150 |
124 | 20191010 | 1100 | ht_order | 10 |
124 | 20191210 | 1200 | order | 140 |
125 | 20200111 | 1201 | ht_order | 10 |
125 | 20200115 | 1202 | order | 130 |
126 | 20200202 | 1205 | order | 110 |
127 | 20200310 | 1206 | ht_order | 10 |
128 | 20200517 | 1207 | ht_order | 10 |
129 | 20190610 | 1208 | ht_order | 10 |
129 | 20190622 | 1209 | order | 180 |
130 | 20200604 | 1210 | ht_order | 10 |
130 | 20200613 | 1211 | order | 120 |
130 | 20200913 | 1212 | order | 150 |
131 | 20200315 | 1213 | ht_order | 10 |
131 | 20200515 | 1005 | order | 130 |
132 | 20200801 | 1215 | ht_order | 10 |
132 | 20200805 | 1220 | order | 120 |
132 | 20200808 | 1225 | order | 130 |
Customer Table
CUSTOMER_KEY | CUSTOMER_EMAIL |
123 | 123@gmail.com |
124 | 124@gmail.com |
125 | 125@gmail.com |
126 | 126@gmail.com |
127 | 127@gmail.com |
128 | 128@gmail.com |
129 | 129@gmail.com |
130 | 130@gmail.com |
131 | 131@gmail.com |
132 | 132@gmail.com |
Date Table
date_key | full_date | weekday_number | ios_week |
20200101 | 2020-01-01 | 3 | 1 |
20200120 | 2020-01-20 | 1 | 4 |
20191010 | 2019-10-10 | 4 | 41 |
20191210 | 2019-12-10 | 2 | 50 |
20200111 | 2020-01-11 | 6 | 2 |
20200115 | 2020-01-15 | 3 | 3 |
20200202 | 2020-02-02 | 7 | 5 |
20200310 | 2020-03-10 | 2 | 11 |
20200517 | 2020-05-17 | 7 | 20 |
20190610 | 2019-06-10 | 1 | 24 |
20190622 | 2019-06-22 | 6 | 25 |
20200604 | 2020-06-04 | 4 | 23 |
20200613 | 2020-06-13 | 6 | 24 |
20200913 | 2020-09-13 | 7 | 37 |
20200315 | 2020-03-15 | 7 | 11 |
20200515 | 2020-05-15 | 5 | 20 |
20200801 | 2020-08-01 | 6 | 31 |
20200805 | 2020-08-05 | 3 | 32 |
20200808 | 2020-08-08 | 6 | 32 |
Big thanks in advance!
@Anonymous,
See the revised measure below. This requires a relationship between the Sales table and Date table.
Count of Order =
SUMX (
ALL ( Customer ),
VAR vCustomer = Customer[customer_key]
VAR vHTOrderRow =
FILTER (
ALL ( Sales ),
Sales[customer_key] = vCustomer
&& Sales[order_type] = "ht_order"
)
VAR vHTOrderDate =
MAXX ( vHTOrderRow, Sales[date_key] )
VAR vOrderRows =
FILTER (
ALL ( Sales ),
Sales[customer_key] = vCustomer
&& Sales[order_type] = "order"
&& Sales[date_key] <= vHTOrderDate + 30
)
RETURN
COUNTROWS ( vOrderRows )
)
Proud to be a Super User!
@Anonymous,
Would you be able to attach screenshots of the data model, visual, and slicers?
Proud to be a Super User!
Hi!
I think it sholud be more correct to use ALL as filter, right? This since we would like to include order_type "ht_order" also outside selected period of time?
Yes, I did a try on the example data so please have a look.
Thanks for helping!
@Anonymous,
Try this. I revised the logic to work at both the total level and the year/month level.
Count of Order =
SUMX (
ALL ( Customer ),
VAR vCustomer = Customer[customer_key]
VAR vHTOrderRow =
FILTER (
ALL ( Sales ),
Sales[customer_key] = vCustomer
&& Sales[order_type] = "ht_order"
)
VAR vHTOrderDate =
MAXX ( vHTOrderRow, Sales[date_key] )
VAR vCountOrderRows =
CALCULATE (
COUNTROWS ( Sales ),
Sales[customer_key] = vCustomer,
Sales[order_type] = "order",
Sales[date_key] <= vHTOrderDate + 30
)
RETURN
vCountOrderRows
)
Proud to be a Super User!
Hi again,
@DataInsights I have two relationships in place (customer_key and date_key), both with cardinality "Many to one".
When trying the formula below I get an error since it show me Count of Order = 6 - no matter what date I select.
Any suggestions on whats being wrong?
Thanks!
@Anonymous,
Try this:
Count of Order =
SUMX (
ALL ( Customer ),
VAR vCustomer = Customer[customer_key]
VAR vHTOrderRow =
FILTER (
ALLSELECTED ( Sales ),
Sales[customer_key] = vCustomer
&& Sales[order_type] = "ht_order"
)
VAR vHTOrderDate =
MAXX ( vHTOrderRow, Sales[date_key] )
VAR vOrderRows =
FILTER (
ALLSELECTED ( Sales ),
Sales[customer_key] = vCustomer
&& Sales[order_type] = "order"
&& Sales[date_key] <= vHTOrderDate + 30
)
RETURN
COUNTROWS ( vOrderRows )
)
Proud to be a Super User!
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |