Count of returning client

[DemoFour]

Hi All, 

I am trying to count up client ID's when they have returned and received another application number. 

The problem I have is that each application is taken through a series of events so that each application has many and varying rows. 

Sample data is as follows

Date	Cleint ID	Application Number	Sequence	Stage	Staff Memeber	Count of Applications (Desired Outcome)
01.01.21	1	1	1	200	A	2
01.01.21	1	1	2	300	A	2
01.01.21	1	1	3	900	A	2
01.01.21	2	2	1	200	B	2
01.01.21	2	2	2	300	B	2
01.01.21	3	3	1	200	C	1
01.02.21	1	4	1	200	C	2
01.02.21	4	5	1	200	A	1
01.02.21	4	5	2	300	A	1
01.02.21	2	6	1	200	C	2
01.03.21	5	7	1	200	A	1

The desired output is to have the number of returning clients in a card as a number, so that a slicer of staff names changes the card. I also want to put this info into a table / matrix to show the dates and returning customers with staff name. 

I hope this makes sense, it seamed easy when I started but I am a bit foxed by not being able to do this - in respsect that I get the wrong number returned due to counting the all the client ID's for each interaction! 

Thanks for any help

[PaulDBrown · Accepted solution]

@DemoFour 

Try:

Number of applicacions by Client ID = 

Number of applicacions by Client ID = CALCULATE(
                                        DISTINCTCOUNT(Table[Application Number],
                                        ALLEXCEPT(Table, FactTable[Cleint ID]))

[DemoFour]

@PaulDBrown 

Thank you, I have just got there!!

Returning Client = 
CALCULATE(
    DISTINCTCOUNT( 'Application Status Change'[Application Number]),
    ALLEXCEPT( 'Application Status Change' , 'Application Status Change'[Client Number])
)

 Thank you for your post. I am just messing with the visuals to see what I can do with it. 

[PaulDBrown]

@DemoFour 

Happy to help!

BTW, if you want to show only "returning" clients (ie, those with more than one application) you can do this easily using the measure in the filter pane: select the table visual, go to "filters on this visual" in the filter pane, add the measure and set the value to "greater than" 1

[DemoFour]

@PaulDBrown 

Thank you for the advice Paul, I have had more of an explore in that section for my visuals and have sorted many issues e.g. days between not working as the visual defaulted to Sum.
Understanding how to use this has effectively, has corrected issues when I thought that there was issues with my Dax. 

I appreciate the help, it's quite a lot to take in learning to use the Power Query and M Language, writing Dax and then bringing the visuals together to show all the right values, understanding the filter context etc. in how the measures and data work when visualising this on the page.  

It really is a great forum, that is lively, informative, helpful and contributes a lot to my learning. 

[amitchandak]

@DemoFour , what is the definition of returning customer?

Check these two blogs how customer retention is done

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

[DemoFour]

@amitchandak 

The definition of returning customer is that they have a new application number. 

What I am trying to achive is a count of the number of application numbers for each client. 

For example 

Duplicate = 
VAR Client = 'Application Status Change'[Client Number]
VAR Result =
COUNTROWS(
    FILTER( 'Application Status Change',
    AND(
        Client = 'Application Status Change'[Client Number] , 'Application Status Change'[Application Status Code] = 200
    )
)
)
RETURN
Result    

 This code works for the count if the application is added at 200 however we have other options (too many to code up this way) so I am looking to count each distinct application for each client (client ID) to see how many times they have had an application. 

Also some new applications are opened before previous ones are closed by different members of staff, so I want this to be flagged up as this needs to stop happening. 

Thank you for the links, I have read the posts and got some ideas for other things I need, but not the question I am asking.