cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yfquirogah
Helper I
Helper I

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!

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

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 )

 

DataInsights_0-1615673210162.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
DataInsights
Super User
Super User

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 )

 

DataInsights_0-1615673210162.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors