Earliest Date with the same value

[yfquirogah]

Hi everybody, 

I have a question regarding a calculated column that I haven't been able to figure out. I have a table similar to this:

Upload Date	ID Person		Grade
1/03/2020	32117464		II
1/02/2020	32117464		III
1/01/2020	32117464		III
1/09/2020	32120099		VII
1/08/2020	32120099		VII
1/07/2020	32120099		VII
1/06/2020	32120099		VII
1/05/2020	32120099		VIII
1/04/2020	32120099		VIII
1/03/2020	32120099		VIII
1/02/2020	32120099		VIII
1/01/2020	32120099		VIII
1/09/2020	32120450		IV
1/08/2020	32120450		IV
1/07/2020	32120450		IV
1/06/2020	32120450		IV
1/05/2020	32120450		V
1/04/2020	32120450		V
1/03/2020	32120450		V
1/02/2020	32120450		VI
1/01/2020	32120450		VI

The employees have a grade that might change overtime, I want to be able to get for each line when was the earliest date the employees started to have the grade they currently have, something like this:

Upload Date	ID Person		Grade	Start Date Grade
1/03/2020	32117464		II	1/03/2020
1/02/2020	32117464		III	1/01/2020
1/01/2020	32117464		III	1/01/2020
1/09/2020	32120099		VII	1/06/2020
1/08/2020	32120099		VII	1/06/2020
1/07/2020	32120099		VII	1/06/2020
1/06/2020	32120099		VII	1/06/2020
1/05/2020	32120099		VIII	1/01/2020
1/04/2020	32120099		VIII	1/01/2020
1/03/2020	32120099		VIII	1/01/2020
1/02/2020	32120099		VIII	1/01/2020
1/01/2020	32120099		VIII	1/01/2020
1/09/2020	32120450		IV	1/06/2020
1/08/2020	32120450		IV	1/06/2020
1/07/2020	32120450		IV	1/06/2020
1/06/2020	32120450		IV	1/06/2020
1/05/2020	32120450		V	1/03/2020
1/04/2020	32120450		V	1/03/2020
1/03/2020	32120450		V	1/03/2020
1/02/2020	32120450		VI	1/01/2020
1/01/2020	32120450		VI	1/01/2020

This is to finally be able to know for how long they have had the same grade:

Upload Date	ID Person		Grade	Start Date Grade	Time with the same Grade (Months)
1/03/2020	32117464		II	1/03/2020	0
1/02/2020	32117464		III	1/01/2020	1
1/01/2020	32117464		III	1/01/2020	0
1/09/2020	32120099		VII	1/06/2020	3
1/08/2020	32120099		VII	1/06/2020	2
1/07/2020	32120099		VII	1/06/2020	1
1/06/2020	32120099		VII	1/06/2020	0
1/05/2020	32120099		VIII	1/01/2020	4
1/04/2020	32120099		VIII	1/01/2020	3
1/03/2020	32120099		VIII	1/01/2020	2
1/02/2020	32120099		VIII	1/01/2020	1
1/01/2020	32120099		VIII	1/01/2020	0
1/09/2020	32120450		IV	1/06/2020	3
1/08/2020	32120450		IV	1/06/2020	2
1/07/2020	32120450		IV	1/06/2020	1
1/06/2020	32120450		IV	1/06/2020	0
1/05/2020	32120450		V	1/03/2020	2
1/04/2020	32120450		V	1/03/2020	1
1/03/2020	32120450		V	1/03/2020	0
1/02/2020	32120450		VI	1/01/2020	1
1/01/2020	32120450		VI	1/01/2020	0

This is the calculated column I have tried but the result is not exactly what I was expecting:

Start grade date =
VAR __IDPerson = 'FTE_2019-2020'[ID Person]
VAR __CurrentDate = 'FTE_2019-2020'[Upload Date]
VAR __CurrentGrade = 'FTE_2019-2020'[Grade]
VAR __PreviousDate = MAXX(FILTER('FTE_2019-2020';'FTE_2019-2020'[Grade]<>__CurrentGrade && 'FTE_2019-2020'[Upload Date] < __CurrentDate);'FTE_2019-2020'[Upload Date])

RETURN 

MAXX(FILTER('FTE_2019-2020';'FTE_2019-2020'[ID Person]=__IDPerson && 'FTE_2019-2020'[Upload Date]=__PreviousDate);'FTE_2019-2020'[Upload Date])

I appreciate any guidance you can provide. Thank you!

[DataInsights · Accepted solution]

Hi @yfquirogah,

Try these calculated columns. (This is m/d/yy format.)

Start Date Grade = 
VAR vPerson = Grades[ID Person]
VAR vGrade = Grades[Grade]
VAR vTable =
    FILTER ( Grades, Grades[ID Person] = vPerson && Grades[Grade] = vGrade )
VAR vResult =
    CALCULATE ( MIN ( Grades[Upload Date] ), vTable )
RETURN
    vResult

Time with the same Grade (Months) = DATEDIFF ( Grades[Start Date Grade], Grades[Upload Date], MONTH )