Forum Discussion
Help with solution for calculation based previous order
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 )
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 )
)
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 [email protected] 124 [email protected] 125 [email protected] 126 [email protected] 127 [email protected] 128 [email protected] 129 [email protected] 130 [email protected] 131 [email protected] 132 [email protected] 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 ) )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!