Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

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
PBIApril_Carousel

Power BI Monthly Update - April 2025

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

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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

Top Solution Authors