Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

dax help

I have two measures --

active customers gross:=

CALCULATE(DISTINCTCOUNT(sales_order_item[id_customer]),sales_order_item[status_code]>=1)

new customer gross:=

CALCULATE(DISTINCTCOUNT(sales_order_item[id_customer]),sales_order[order_rank]=1,sales_order_item[status_code]>=1)

 

 

i want a dax query to find the new customer behaviour.. a customer who is new for month of january 2020, whether he had purchased anything on next mont month (feb 2020)...so basically i want to find the how many new customers from january have became active customers in february...

 

repeat customer = active customer- new customer

but i just dont want repeat customer from active customer, i want repeat customer from last month's new customer..

Need Help

9 Replies

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    Hi Anonymous 

    You can probably use some of the set functions (INTERSECT ()..) but your explanation is rather confusing and I do not quite get what you actually need. Can you share some sample data and provide an example based on it showinfg the expected result?

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    i want a dax query to find the new customer behaviour.. a customer who is new for month of january 2020, whether he had purchased anything on next mont month (feb 2020)...so basically i want to find the how many new customers from january have became active customers in february...

     

    monthactive customernew customer
    jan110462461
    feb97032129
    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      Anonymous , assuming Feb -2020 is selected as, try a new measure

      measure =
      var _min = minx(allselected(Date),Date[Date])
      var _max= maxx(allselected(Date),Date[Date])

      var _minL = eomonth(minx(allselected(Date),Date[Date]),-2)+1
      var _maxL= eomonth(maxx(allselected(Date),Date[Date]),-1)
      var _cust =FIRSTNONBLANKVALUE(table[customer],Min(Date[date]))
      var _cnt =countx(filter(allselected(Date),Date[Date] <=_maxL && Date[Date]t>= _minL ), table[customer])
      return
      calculate(count(table[customer]),values(table[customer]) , filter(allselected(Table),(_cust <=_maxL && _cust>= _minL ) && not(isblank)))

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        hi amit,

         

        Thanks for your help. i was trying to create the measures as told by you, but i am facing error while creating var _cnt measure..countx function after having a filter on date doesnt understand table[cust] .

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      6 years ago

      Hi Anonymous 

      Please check if there is any answer or link helpful.

      If not, feel free to ask me.

      By the way, new customer for one month is the customer who didn'y buy anything before this month, active customers are the ones who buy anything before(last month, or last n months),

      Is my understanding correct?

       

      Best Regards

      Maggie

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        yes, new customers are customers who have not purchased anything before. Active customers are customers who have brought something before. we want to analyse new customers behaviour for next 3 months. i mean if any customer is new in january, how they are behaving in feb and march..are they coming back to us in feb or march or not