Forum Discussion

thoberre's avatar
thoberre
Regular Visitor
9 years ago
Solved

Customer cohort analysis

I have a challenge I cannot wrap my head around properly.   I have a pretty common ecommerce/ordersystem database with customers, orders, and orderitems tables in it.     A customer has a ...
Need Help
Tutorial Requests
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi thoberre,

     

    In PowerBI, it seems that we can not create a visual exactly the same as the structure of the mockup you post to show the cohort analysis. But you can try the following workaround to make a customer cohort analysis.

     

    The relationships between these tables are like below:

     

    Then, in User table, create some calculated columns and measures, please refer to the below formulas.

    Calculated columns:

    SignUpWeek = WEEKNUM(User[created_at])

    Diff = [LastOrderWeek]-User[SignUpWeek]

    Week1 = IF(User[Diff]>=1,1,0)
    Week2 = IF(User[Diff]>=2,1,0)
    Week3 = IF(User[Diff]>=3,1,0)
    Week4 = IF(User[Diff]>=4,1,0)

     

    Measures:

    LastOrderWeek = WEEKNUM(MAX('Order'[delivery_date]))

1 =
        CALCULATE (
            COUNTA ( User[ID] ),
            FILTER (
                ALL ( User ),
                ( WEEKNUM ( User[created_at] ) = WEEKNUM ( MAX ( User[created_at] ) ) )
            )
        )
2 = SUM(User[Week1])
3 = SUM(User[Week2])
    4 = SUM(User[Week3])
    >4 = SUM(User[Week4])

    Insert a table visual, drag relative columns to it.

     

    If you have any question, please feel free to ask.

     

    Best regards,
    Yuliana Gu