Forum Discussion

yfquirogah's avatar
yfquirogah
Helper I
5 years ago
Solved

Earliest Date with the same value

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 DateID Person Grade
1/03/202032117464 II
1/02/202032117464 III
1/01/202032117464 III
1/09/202032120099 VII
1/08/202032120099 VII
1/07/202032120099 VII
1/06/202032120099 VII
1/05/202032120099 VIII
1/04/202032120099 VIII
1/03/202032120099 VIII
1/02/202032120099 VIII
1/01/202032120099 VIII
1/09/202032120450 IV
1/08/202032120450 IV
1/07/202032120450 IV
1/06/202032120450 IV
1/05/202032120450 V
1/04/202032120450 V
1/03/202032120450 V
1/02/202032120450 VI
1/01/202032120450 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 DateID Person GradeStart Date Grade
1/03/202032117464 II1/03/2020
1/02/202032117464 III1/01/2020
1/01/202032117464 III1/01/2020
1/09/202032120099 VII1/06/2020
1/08/202032120099 VII1/06/2020
1/07/202032120099 VII1/06/2020
1/06/202032120099 VII1/06/2020
1/05/202032120099 VIII1/01/2020
1/04/202032120099 VIII1/01/2020
1/03/202032120099 VIII1/01/2020
1/02/202032120099 VIII1/01/2020
1/01/202032120099 VIII1/01/2020
1/09/202032120450 IV1/06/2020
1/08/202032120450 IV1/06/2020
1/07/202032120450 IV1/06/2020
1/06/202032120450 IV1/06/2020
1/05/202032120450 V1/03/2020
1/04/202032120450 V1/03/2020
1/03/202032120450 V1/03/2020
1/02/202032120450 VI1/01/2020
1/01/202032120450 VI1/01/2020

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

Upload DateID Person GradeStart Date GradeTime with the same Grade (Months)
1/03/202032117464 II1/03/20200
1/02/202032117464 III1/01/20201
1/01/202032117464 III1/01/20200
1/09/202032120099 VII1/06/20203
1/08/202032120099 VII1/06/20202
1/07/202032120099 VII1/06/20201
1/06/202032120099 VII1/06/20200
1/05/202032120099 VIII1/01/20204
1/04/202032120099 VIII1/01/20203
1/03/202032120099 VIII1/01/20202
1/02/202032120099 VIII1/01/20201
1/01/202032120099 VIII1/01/20200
1/09/202032120450 IV1/06/20203
1/08/202032120450 IV1/06/20202
1/07/202032120450 IV1/06/20201
1/06/202032120450 IV1/06/20200
1/05/202032120450 V1/03/20202
1/04/202032120450 V1/03/20201
1/03/202032120450 V1/03/20200
1/02/202032120450 VI1/01/20201
1/01/202032120450 VI1/01/20200

 

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!

Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    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 )

     

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    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 )