using a start and end date to determine active status at yearly milestones

[Anonymous]

I have a dataset for students with Course name, Start date, End date and current status fields.   looking back over the last 5 years start dates I want to display if the student was or is still active at their 1,2 & 3 year marks.

I have a Measure which is very close to showing what I wanted (below), however when validating I realised its applying the current status retrospectively for older dates. i need it to show a student who started Jan 2020 and withdrew Feb 2021 as active at the one year actual but not in the two or three year milestones. the measure below doesnt count correctly in this example. 

(I have three measures like this one below, changing the -1 value to match the year)

1 year active =
CALCULATE (
    DISTINCTCOUNT ( registrations[StudentID] ),
    DATEADD ( _DIM_Calendar[Date], -1, YEAR ),
    OR (
        registrations[status] = "Completed",
        registrations[status] = "In Progress"
    )
)

I can’t quite get my head around how to use the start and end date to calculate if a student was still active after 1 year, 2 years and 3 years - any guidance would be appreciated.

[Anonymous · Accepted solution]

Replying if someone is searching for a similar problem.
I found a work around using power query - i created a count of years column between start and end dates and rounded down.  Then i added in a filter to the above to include if the years qualified.   not very elegant but gets the job done. 

Dax measure Looks like this: 

12m Actual =

CALCULATE (

DISTINCTCOUNT ( registrations2[tyims_trainingcontractid] ),

SAMEPERIODLASTYEAR ( _DIM_Calendar[Date] ),

OR (

registrations2[status Group] = "Completed",

registrations2[status Group] = "In Progress"

),

OR(OR(

registrations2[Years] >= 1,

registrations2[status Group] = "In Progress"),

registrations2[status Group] = "Completed"

))

[amitchandak]

@Anonymous , Check if this blog on a similar topic can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.