Get number of returning employers

[SBR1D]

Hi All

I have googled this issue but none of the results match my scenario fully so i thought i'd ask here.

I have the following dataset and I want to have a table of returning employers. For example in the dataset JD sports at the time of November 2023 had 0 learners but in December 2023 they had a new learner so they are now a returning employer.  I'm really struggling with the logic and if i need to expand on my Lost employer or New Employer measures.

Lost employer 

Lost employers = VAR  Lost =  CALCULATETABLE(SUMMARIZE(LearningPlan,LearningPlan[Employer name]),(LearningPlan[Status]="In Progress"))

                RETURN
                COUNTROWS(FILTER(SUMMARIZE(LearningPlan,LearningPlan[Employer name]),
                NOT LearningPlan[Employer name] IN Lost))

New employer 

New Employer = 
    VAR Current_Employer = CALCULATETABLE(SUMMARIZE(LearningPlan,LearningPlan[Employer name]), DATESBETWEEN(LearningPlan[Start Date], MIN(LearningPlan[Start Date]), EOMONTH(TODAY(), -1)))

    RETURN
    COUNTROWS(FILTER(SUMMARIZE(LearningPlan,LearningPlan[Employer name]),NOT LearningPlan[Employer name] IN Current_Employer))

Dataset 

LearnerID	LearnerName	Start Date	Planned End	Status	EmployerID	Employer name	Completion Date	Withdrawal Date	Include in ILR?
1	Bob J	01-Mar-20	19-Jun-21	Withdrawn	1	JD Sports		04-Feb-20	Yes
2	Sidney D	02-Apr-20	21-Jul-21	Withdrawn	1	JD Sports		13-Jan-21	Yes
3	Josh A	01-Mar-20	19-Jun-21	Withdrawn	1	JD Sports		19-Jun-20	Yes
4	Christian R	14-Mar-20	02-Jul-21	Withdrawn	1	JD Sports		17-Jul-20	Yes
5	Tommy H	18-Mar-21	06-Jul-22	Withdrawn	2	Barrys Emporium		10-Jun-21	Yes
6	Bruce D	19-Sep-20	07-Jan-22	Withdrawn	2	Barrys Emporium		11-Nov-20	Yes
7	James H	16-Sep-23	03-Jan-25	Withdrawn	2	Barrys Emporium		19-Sep-23	Yes
8	Angus Y	12-Dec-20	01-Apr-22	Withdrawn	2	Barrys Emporium		03-Mar-23	Yes
9	Marshall M	03-Sep-19	21-Dec-20	Withdrawn	3	Target		10-Oct-20	Yes
10	Barry K	15-Jun-23	02-Oct-24	Withdrawn	3	Target		08-Aug-23	Yes
11	Tony K	30-Apr-19	17-Aug-20	Withdrawn	3	Target		29-May-19	No
12	Emily D	11-Jan-20	30-Apr-21	Withdrawn	3	Target		02-Feb-20	Yes
13	Rebecca R	08-Dec-23	27-Mar-25	Withdrawn	4	McDonalds		11-Apr-24	Yes
14	Jane S	06-Feb-23	26-May-24	Withdrawn	4	McDonalds		07-Feb-23	Yes
15	Amy I	17-Jan-24	06-May-25	Withdrawn	4	McDonalds		11-May-24	Yes
16	Erin S	02-Feb-23	22-May-24	Withdrawn	4	McDonalds		11-Feb-23	Yes
17	Bertrina D	01-Feb-22	22-May-26	Withdrawn	5	KFC		17-Feb-22	Yes
18	John D	03-Jun-22	21-Sep-23	Withdrawn	5	KFC		04-Aug-22	Yes
19	Tim P	03-Jul-23	21-Oct-25	Withdrawn	5	KFC		10-Jul-23	No
20	Maxine A	11-Nov-23	28-Feb-25	Withdrawn	5	KFC		07-Dec-23	Yes
21	Jasmine P	11-Nov-23	24-Apr-24	In Progress	2	Barrys Emporium			No
22	Jack D	30-Apr-19	10-Dec-20	In Progress	5	KFC			No
23	Rob K	01-Jan-18	23-May-23	In Progress	3	Target			No
24	Jessy J	15-Mar-17	23-May-23	In Progress	3	Target			No
25	Chad M	25-Apr-17	28-Feb-24	In Progress	3	Target			No
26	Mo I	29-Dec-23	06-Mar-25	In Proress	10	Burger King			Yes
27	Jason N	09-Jan-24	06-Jun-25	In Progress	11	Gillete			Yes
28	Jenny J	22-Dec-23	09-Aug-25	In Progress	1	JD Sports			Yes

[Sahir_Maharaj]

Hello @SBR1D,

Can you please try this:

1. Employers with Current Learners

Current Employers = 
CALCULATE(
    DISTINCTCOUNT(LearningPlan[EmployerID]),
    LearningPlan[Status] = "In Progress"
)

2. Employers with Past Learners

Past Employers = 
CALCULATE(
    DISTINCTCOUNT(LearningPlan[EmployerID]),
    LearningPlan[Status] <> "In Progress"
)

3. Returning Employers

Returning Employers = 
CALCULATE(
    COUNTROWS(
        INTERSECT(
            VALUES(LearningPlan[EmployerID]),
            CALCULATETABLE(VALUES(LearningPlan[EmployerID]), LearningPlan[Status] <> "In Progress")
        )
    ),
    LearningPlan[Status] = "In Progress"
)

[SBR1D]

Hi, thanks for your answer but i don't think the returning employers measure is logically correct. 

A returning employer won't have 0 learners <> " In Progress" and  Learners "In Progress" at the same time. I think I need to get learners who had 0 learners <> " In Progress" x number of months before today and then employers with learners in progress from today-7 days  to the current day.

I have tried the following but it doesn't give correct result. Any ideas please?

lost emps calc = CALCULATE([Lost employers],DATESBETWEEN(LearningPlan[Withdrawal Date],EDATE(TODAY(),-3),TODAY()-7))