Forum Discussion
Line Graph Over Time: When the Column Header Contains the Year, and the cells contain values.
If this is posted somewhere else, please direct me.
Perhaps I dont know the terminology to pinpoint what I am chasing.
I am trying to map out a student markbook to show their growth over time.
| Student Name | Semester 1, 2020 | Semester 2, 2020 | Semester 1, 2021 | Semester 2, 2021 | 2022; Semester 2 |
| Brad Pitt | 3.5 | 2.8 | 4 | 2.7 | 4 |
| George Clooney | 2.8 | 2.1 | 3 | 3.1 | 3.8 |
| Krusty the Clown | 2.6 | 1.0 | 2 | 2.1 | 1.9 |
*Names and grade are fictitious.
I would like a graph that maps out their progress over time in a line graph.
I can do when the Dates are in a column, but not as a Row.
Much appreciated.
silasmiddleton , Unpivot this data. You should get semester as one column
Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g
Create a new table for the semester, example in Dax
semester = distinct(Table[semester])
Create rank on semester as a new column in the semester table
semester rank = rankx( semester , semester [semester],,asc,dense)
then create measure like
This semester= CALCULATE(sum('Table'[Score]), FILTER(ALL('semester'),'Date'[semester Rank]=max('Date'[semester Rank])))
Last semester= CALCULATE(sum('Table'[Score]), FILTER(ALL('semester'),'Date'[semester Rank]=max('Date'[semester Rank])-1))Same logic as week or custom period
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145sJoin with semester of table
1 Reply
silasmiddleton , Unpivot this data. You should get semester as one column
Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g
Create a new table for the semester, example in Dax
semester = distinct(Table[semester])
Create rank on semester as a new column in the semester table
semester rank = rankx( semester , semester [semester],,asc,dense)
then create measure like
This semester= CALCULATE(sum('Table'[Score]), FILTER(ALL('semester'),'Date'[semester Rank]=max('Date'[semester Rank])))
Last semester= CALCULATE(sum('Table'[Score]), FILTER(ALL('semester'),'Date'[semester Rank]=max('Date'[semester Rank])-1))Same logic as week or custom period
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145sJoin with semester of table