Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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 Date | ID Person | Grade | |
1/03/2020 | 32117464 | II | |
1/02/2020 | 32117464 | III | |
1/01/2020 | 32117464 | III | |
1/09/2020 | 32120099 | VII | |
1/08/2020 | 32120099 | VII | |
1/07/2020 | 32120099 | VII | |
1/06/2020 | 32120099 | VII | |
1/05/2020 | 32120099 | VIII | |
1/04/2020 | 32120099 | VIII | |
1/03/2020 | 32120099 | VIII | |
1/02/2020 | 32120099 | VIII | |
1/01/2020 | 32120099 | VIII | |
1/09/2020 | 32120450 | IV | |
1/08/2020 | 32120450 | IV | |
1/07/2020 | 32120450 | IV | |
1/06/2020 | 32120450 | IV | |
1/05/2020 | 32120450 | V | |
1/04/2020 | 32120450 | V | |
1/03/2020 | 32120450 | V | |
1/02/2020 | 32120450 | VI | |
1/01/2020 | 32120450 | 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 Date | ID Person | Grade | Start Date Grade | |
1/03/2020 | 32117464 | II | 1/03/2020 | |
1/02/2020 | 32117464 | III | 1/01/2020 | |
1/01/2020 | 32117464 | III | 1/01/2020 | |
1/09/2020 | 32120099 | VII | 1/06/2020 | |
1/08/2020 | 32120099 | VII | 1/06/2020 | |
1/07/2020 | 32120099 | VII | 1/06/2020 | |
1/06/2020 | 32120099 | VII | 1/06/2020 | |
1/05/2020 | 32120099 | VIII | 1/01/2020 | |
1/04/2020 | 32120099 | VIII | 1/01/2020 | |
1/03/2020 | 32120099 | VIII | 1/01/2020 | |
1/02/2020 | 32120099 | VIII | 1/01/2020 | |
1/01/2020 | 32120099 | VIII | 1/01/2020 | |
1/09/2020 | 32120450 | IV | 1/06/2020 | |
1/08/2020 | 32120450 | IV | 1/06/2020 | |
1/07/2020 | 32120450 | IV | 1/06/2020 | |
1/06/2020 | 32120450 | IV | 1/06/2020 | |
1/05/2020 | 32120450 | V | 1/03/2020 | |
1/04/2020 | 32120450 | V | 1/03/2020 | |
1/03/2020 | 32120450 | V | 1/03/2020 | |
1/02/2020 | 32120450 | VI | 1/01/2020 | |
1/01/2020 | 32120450 | VI | 1/01/2020 |
This is to finally be able to know for how long they have had the same grade:
Upload Date | ID Person | Grade | Start Date Grade | Time with the same Grade (Months) | |
1/03/2020 | 32117464 | II | 1/03/2020 | 0 | |
1/02/2020 | 32117464 | III | 1/01/2020 | 1 | |
1/01/2020 | 32117464 | III | 1/01/2020 | 0 | |
1/09/2020 | 32120099 | VII | 1/06/2020 | 3 | |
1/08/2020 | 32120099 | VII | 1/06/2020 | 2 | |
1/07/2020 | 32120099 | VII | 1/06/2020 | 1 | |
1/06/2020 | 32120099 | VII | 1/06/2020 | 0 | |
1/05/2020 | 32120099 | VIII | 1/01/2020 | 4 | |
1/04/2020 | 32120099 | VIII | 1/01/2020 | 3 | |
1/03/2020 | 32120099 | VIII | 1/01/2020 | 2 | |
1/02/2020 | 32120099 | VIII | 1/01/2020 | 1 | |
1/01/2020 | 32120099 | VIII | 1/01/2020 | 0 | |
1/09/2020 | 32120450 | IV | 1/06/2020 | 3 | |
1/08/2020 | 32120450 | IV | 1/06/2020 | 2 | |
1/07/2020 | 32120450 | IV | 1/06/2020 | 1 | |
1/06/2020 | 32120450 | IV | 1/06/2020 | 0 | |
1/05/2020 | 32120450 | V | 1/03/2020 | 2 | |
1/04/2020 | 32120450 | V | 1/03/2020 | 1 | |
1/03/2020 | 32120450 | V | 1/03/2020 | 0 | |
1/02/2020 | 32120450 | VI | 1/01/2020 | 1 | |
1/01/2020 | 32120450 | VI | 1/01/2020 | 0 |
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!
Solved! Go to Solution.
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 )
Proud to be a 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 )
Proud to be a Super User!
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
105 | |
69 | |
48 | |
47 | |
47 |