Retrieve the maximum date in a column where a value is appearing for the first time in another colum

[JoSwinnen1]

Hi all,

I have a table with for each year a student start at the university a row appears. I want to find out how long it takes before the student is graduating. But there are some difficulities. A lot of them are already tackled, but there are still some exceptions which are not covered yet. For instance a student can have a second diploma in the same education, but I want to calculate the duration based on the first diploma. 

This is part of my table: 

Studentnumber   Education      StartDate       Enddate              Diploma

00001                       X            15/09/2016        15/06/2017

00001                       X            15/09/2017        15/06/2018

00001                       X            15/09/2018        15/06/2019           Yes

00001                       X            15/09/2019        15/06/2020           Yes

Duration =

if(

vw_PowerBI[Diploma]="Yes",

Value(

calculate(

datediff(min(vw_powerBI[startdate]),max(vw_powerBI[enddate]),DAY)/365,

filter(vw_powerBI,vw_powerBI[studentnumber]=earlier(vw_powerBI[studentnumber])

),

filter(vw_powerBI,vw_powerBI[education]=earlier(vw_powerBI[education])

)

)

),

0)

But I will get 4 instead of 3 as a result, how can I make sure that the maximum of enddate is calculated based on the first yes? 

I hope it is clear...

Kind regards,

Jo 

[v-yangliu-msft · Accepted solution]

Hi  @JoSwinnen1 ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _start=
MINX(FILTER(ALL('Table'),
'Table'[Studentumnber]=MAX('Table'[Studentumnber])),[StartDate])
var _end=
MINX(FILTER(ALL('Table'),
'Table'[Studentumnber]=MAX('Table'[Studentumnber])&&'Table'[Diploma]="Yes"),[EndDate])
return
DATEDIFF(_start,_end,DAY)

2. Result:

If you need pbix, please click here.

Retrieve the maximum date in a column where a value is appearing for the first time in another colum...

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@JoSwinnen1 , Create measure like

Sumx(Summarize(Filter( vw_PowerBI, vw_PowerBI[Diploma]="Yes"), vw_powerBI,vw_powerBI[studentnumber], vw_powerBI[education] , "_1", datediff(min(vw_powerBI[startdate]),max(vw_powerBI[enddate]),DAY)/365 ), [_1])

[JoSwinnen1]

Thank you for your reply. I don't understand the formula. Can you explain? Because it seems that it is not giving the appropriate result. 

Maybe another difficulty: I want to create classes also (<2 years, 2-4 years, ...), is it not better to create a calculated column then?