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
)

     

     

     

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

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 

      • DataInsights's avatar
        DataInsights
        Super User
        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
)