Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Active / LeavingCustomers Measures

Hello,    I am creating column chart by years/yearmonths with customers segmented by their activity:   Active Customer:  customer has status 'active'  up to 15 month after his order.  Measure is ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Anonymous - Try this out:

    Leaving Customers =
  VAR __Today = TODAY()
  VAR __Table = 
    ADDCOLUMNS(
      ADDCOLUMNS(
        SUMMARIZE('Table',[CustomerEmail],"End15",MAX([Until 15M])),
        "16",EOMONTH([End15,1])
      ),
      "Leaving",IF(__Today > [End15] && __Today <=[16],1,0)
    )
RETURN
  COUNTROWS(FILTER(__Table,[Leaving]=1))
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous,

    I'd like to suggest you take a look at following link about hr employee analytic if it meet your requirement:

    HR Data - Slowly Changing Dimensions 

    Regards,

    Xiaoxin Sheng

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous - Not exactly following this, so when would those customers be considered inactive?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    hello,

     

    Greg_Deckler 

    active- 0 - 15

    leaving - 16

    inactive - 17+

     

    inactive is easy to calculate as 'total cumulative' - 'active 17'

    problem is just 16 months - customer gets this status when it is 16 months after his order and there were no other orders in this period

     

     

     

    amitchandak 

    it doesnt' count customers but sums revenue and the second measure not works properly

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Anonymous - Try this out:

      Leaving Customers =
  VAR __Today = TODAY()
  VAR __Table = 
    ADDCOLUMNS(
      ADDCOLUMNS(
        SUMMARIZE('Table',[CustomerEmail],"End15",MAX([Until 15M])),
        "16",EOMONTH([End15,1])
      ),
      "Leaving",IF(__Today > [End15] && __Today <=[16],1,0)
    )
RETURN
  COUNTROWS(FILTER(__Table,[Leaving]=1))