How do I calculate a % of a group with improved scores over time

[laura_ESNH]

I have a group of people that are accessed on a task on a daily basis. They are give an score from 1 to 5. If they are not assessed on a particular day, they are given a zero. I need to calculate the % of the group that has shown improvement over time. 

My data looks like this...

ID	Assessement Date	GoalNeed	Goal Need Score
ID000444	8/1/2019	Post-Secondary Education/Training Goal	0
ID000444	9/17/2019	Post-Secondary Education/Training Goal	0
ID000444	9/24/2019	Post-Secondary Education/Training Goal	0
ID000444	9/25/2019	Post-Secondary Education/Training Goal	1.5
ID000444	9/26/2019	Post-Secondary Education/Training Goal	0
ID000444	10/8/2019	Post-Secondary Education/Training Goal	0
ID000444	10/9/2019	Post-Secondary Education/Training Goal	2.33
ID000444	10/15/2019	Post-Secondary Education/Training Goal	0
ID000444	11/15/2019	Post-Secondary Education/Training Goal	0
ID000444	12/16/2019	Post-Secondary Education/Training Goal	0
ID000444	12/17/2019	Post-Secondary Education/Training Goal	0
ID000444	12/18/2019	Post-Secondary Education/Training Goal	1
ID000444	1/22/2020	Post-Secondary Education/Training Goal	0
ID000444	1/23/2020	Post-Secondary Education/Training Goal	4
ID000456	8/1/2019	Post-Secondary Education/Training Goal	3
ID000456	8/6/2019	Post-Secondary Education/Training Goal	4
ID000456	9/3/2019	Post-Secondary Education/Training Goal	2
ID000456	9/5/2019	Post-Secondary Education/Training Goal	2
ID000456	9/10/2019	Post-Secondary Education/Training Goal	2
ID000456	9/12/2019	Post-Secondary Education/Training Goal	2
ID000456	9/17/2019	Post-Secondary Education/Training Goal	2
ID000456	9/19/2019	Post-Secondary Education/Training Goal	2
ID000456	9/24/2019	Post-Secondary Education/Training Goal	2
ID000456	9/26/2019	Post-Secondary Education/Training Goal	2
ID000456	10/1/2019	Post-Secondary Education/Training Goal	2
ID000456	10/3/2019	Post-Secondary Education/Training Goal	2
ID000456	10/8/2019	Post-Secondary Education/Training Goal	3
ID000456	10/10/2019	Post-Secondary Education/Training Goal	2
ID000456	10/15/2019	Post-Secondary Education/Training Goal	2
ID000456	10/17/2019	Post-Secondary Education/Training Goal	2
ID000456	10/22/2019	Post-Secondary Education/Training Goal	3
ID000456	10/24/2019	Post-Secondary Education/Training Goal	3
ID000456	10/29/2019	Post-Secondary Education/Training Goal	3
ID000456	10/31/2019	Post-Secondary Education/Training Goal	2
ID000456	11/5/2019	Post-Secondary Education/Training Goal	3
ID000456	11/7/2019	Post-Secondary Education/Training Goal	0
ID000456	11/12/2019	Post-Secondary Education/Training Goal	0
ID000456	11/14/2019	Post-Secondary Education/Training Goal	3
ID000456	11/19/2019	Post-Secondary Education/Training Goal	3
ID000456	11/21/2019	Post-Secondary Education/Training Goal	2.33
ID000456	11/26/2019	Post-Secondary Education/Training Goal	3
ID000456	12/3/2019	Post-Secondary Education/Training Goal	0
ID000456	12/5/2019	Post-Secondary Education/Training Goal	2.67
ID000456	12/10/2019	Post-Secondary Education/Training Goal	3
ID000456	12/12/2019	Post-Secondary Education/Training Goal	3
ID000456	12/17/2019	Post-Secondary Education/Training Goal	4
ID000456	12/19/2019	Post-Secondary Education/Training Goal	2.67
ID000456	1/2/2020	Post-Secondary Education/Training Goal	3.67
ID000456	1/7/2020	Post-Secondary Education/Training Goal	4
ID000456	1/9/2020	Post-Secondary Education/Training Goal	3.33
ID000456	1/14/2020	Post-Secondary Education/Training Goal	2.67
ID000456	1/16/2020	Post-Secondary Education/Training Goal	3.33
ID000456	1/21/2020	Post-Secondary Education/Training Goal	0
ID000456	1/23/2020	Post-Secondary Education/Training Goal	3.33
ID000456	1/28/2020	Post-Secondary Education/Training Goal	3.33
ID000456	1/30/2020	Post-Secondary Education/Training Goal	3.33
ID000462	8/5/2019	Post-Secondary Education/Training Goal	2
ID000462	8/6/2019	Post-Secondary Education/Training Goal	0
ID000462	8/7/2019	Post-Secondary Education/Training Goal	2
ID000462	8/12/2019	Post-Secondary Education/Training Goal	2
ID000462	8/13/2019	Post-Secondary Education/Training Goal	0
ID000462	8/14/2019	Post-Secondary Education/Training Goal	0
ID000462	8/19/2019	Post-Secondary Education/Training Goal	0
ID000462	8/20/2019	Post-Secondary Education/Training Goal	0
ID000462	8/21/2019	Post-Secondary Education/Training Goal	0
ID000462	2/24/2020	Post-Secondary Education/Training Goal	0
ID000462	2/25/2020	Post-Secondary Education/Training Goal	0
ID000462	2/26/2020	Post-Secondary Education/Training Goal	0
ID000462	2/27/2020	Post-Secondary Education/Training Goal	0
ID000462	2/28/2020	Post-Secondary Education/Training Goal	0
ID000462	4/20/2020	Post-Secondary Education/Training Goal	4
ID000462	4/21/2020	Post-Secondary Education/Training Goal	3.5
ID000462	4/22/2020	Post-Secondary Education/Training Goal	4
ID000462	4/23/2020	Post-Secondary Education/Training Goal	4
ID000462	4/24/2020	Post-Secondary Education/Training Goal	4
ID000462	4/28/2020	Post-Secondary Education/Training Goal	4
ID000462	4/29/2020	Post-Secondary Education/Training Goal	4
ID000462	4/30/2020	Post-Secondary Education/Training Goal	4
ID000462	5/4/2020	Post-Secondary Education/Training Goal	5
ID000462	5/5/2020	Post-Secondary Education/Training Goal	4
ID000462	5/6/2020	Post-Secondary Education/Training Goal	4
ID000462	5/7/2020	Post-Secondary Education/Training Goal	4
ID000462	5/8/2020	Post-Secondary Education/Training Goal	4

I can easily create a line graph that visually shows the progress of an individual or group. I'm struggling with how to turn this into a metric to report the number of individuals that have demonstrated progress on the goal over the course of the assessment period. 

Any guidance would be greately appreciated. 

[v-lionel-msft]

Hi @laura_ESNH ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[laura_ESNH]

Thank you @v-lionel-msft. I actually already know how to do the line graph. It's turning it into a "reportable" metric (i.e. x% of group demonstrated improvement on their goal), that I'm struggling with. 

[v-lionel-msft]

Hi @laura_ESNH ,

Please provide an expected output table, that is, what data do you hope to obtain through calculation?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@laura_ESNH , You can compare score period over period. with a date table

MTD Score= CALCULATE(SUM(Table[Score]),DATESMTD('Date'[Date]))
last MTD Score= CALCULATE(SUM(Table[Score]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

diff = [MTD Score]-[last MTD Score]
diff % = divide([MTD Score]-[last MTD Score],[last MTD Score])

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[laura_ESNH]

Thank you, @amitchandak. This worked great. 

Do you have any suggestions for the following scenario?

I have a score for every month of the year. I want to calculuate the difference between the score for January and the score they finish with in December. So, it's not really month over month, it's from the start of the period to the end. Thoughts? ,