Reply
yfquirogah
Helper I
Helper I
Partially syndicated - Outbound

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

Syndicated - Outbound

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

Syndicated - Outbound

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!




avatar user

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)