Forum Discussion

CSpina's avatar
CSpina
Icon for Helper III rankHelper III
5 years ago
Solved

HOw to count active customers using a customer log

Hy, guys and girls.

 

I have two tables, customers and customers log. The last one shows when the customer gets active (1) or inactive (0). The customer can be inactive or active many times in the year. I need to build a measure to show how many customer are active per month.

 

 

Customer Log

 

 

The objetive is building a graph like: 

dCustomers

let
Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIuLS7Jz00tUnAM9lOK1YlWMkIVDAYLGiMLBgQEgAVNkAXD3FzAgqbIgpEhoWBBM1Tt/kqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, name = _t]),
#"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"id", Int64.Type}})
in
#"Tipo Alterado"

 

dCustomersLog

let
Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdDRCcAgDATQXfItNBdjobOI+6/RKi1eNaA/j3A5UqtAkmj/OJ5niktaqmLENjnH7EuI6eBC7JNP4jK5NwExrQStfKc9ZiPO/yaImyBuohyC7SbEWLOxnerjdgM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [customer_id = _t, active = _t, date = _t]),
#"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"customer_id", Int64.Type}, {"active", Int64.Type}, {"date", type date}})
in
#"Tipo Alterado"

 

Thanks in advance.

Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    CSpina see attached, there is two versions of the measures.

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    There are many tables in the report, just focus, dCustomer, dCustomerLog and Calendar table.

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • CSpina's avatar
      CSpina
      Icon for Helper III rankHelper III
      5 years ago

      Thanks, Parry2k.

       

      Both measures works for me. But how to calculate de active customers acumulative using customers log. I need to calculate how many active customers until date.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    1. Create a Calendar Table and build a replationship from the Date column of the Customer Log table to the Date column of the Calendar Table
    2. In the Calendar Table, write calculated column formulas to extract Year, Month number and Month name.  Sort the Month name column by the Month number column
    3. To your visual, drag Year and Month name
    4. Write these calculated column formulas

    Number of Customers = distinctcount(Data[customer_id])

    Active customers = calculate([Number of Customers],data[active]=1)

    Drag the Active customers measure to the visual.

    Hope this helps.