Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Help with solution for calculation based previous order

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 "...
Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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_keydate_keyorder_numberorder_typesales
123202001011001ht_order10
123202001201020order150
124201910101100ht_order10
124201912101200order140
125202001111201ht_order10
125202001151202order130
126202002021205order110
127202003101206ht_order10
128202005171207ht_order10
129201906101208ht_order10
129201906221209order180
130202006041210ht_order10
130202006131211order120
130202009131212order150
131202003151213ht_order10
131202005151005order130
132202008011215ht_order10
132202008051220order120
132202008081225order130

 

Customer Table

CUSTOMER_KEYCUSTOMER_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_keyfull_dateweekday_numberios_week
202001012020-01-0131
202001202020-01-2014
201910102019-10-10441
201912102019-12-10250
202001112020-01-1162
202001152020-01-1533
202002022020-02-0275
202003102020-03-10211
202005172020-05-17720
201906102019-06-10124
201906222019-06-22625
202006042020-06-04423
202006132020-06-13624
202009132020-09-13737
202003152020-03-15711
202005152020-05-15520
202008012020-08-01631
202008052020-08-05332
202008082020-08-08632

 

 

Big thanks in advance!

 

 

DataInsights's avatar
DataInsights
Super User
5 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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!

     
     
     

     

     

    • DataInsights's avatar
      DataInsights
      Super User
      5 years ago

      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 )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    DataInsights  - Unfortunately, getting still the same output..

    • DataInsights's avatar
      DataInsights
      Super User
      5 years ago

      Anonymous,

       

      Would you be able to attach screenshots of the data model, visual, and slicers?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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. 

        ReportModel

         

         

         

         

         

         

         

        Thanks for helping!

        DataInsights