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

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