The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
82 | |
65 | |
53 | |
52 |
User | Count |
---|---|
128 | |
115 | |
80 | |
65 | |
63 |