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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.