Rolling weighted value (other column) for project (aggregating the last timestamps of subprojects)

[Riddler]

Hi, I am aggregating data for development projects. These projects consist of different repositories, which give me metadata for timestamps when scanning the current status of a repository. (example of result table in answers)

repo.repositoryName	repo.uid	repo.loc	repo.scantimestamp	repo.overallRating	repo.locXOverallRating
Kafka	7918f57464e4595884652054b12f1735	136970	29.04.2019 14:30	2,79	480764,7
lgv	f0498d40214513effa4c415c83a161c6	111618	29.04.2019 14:27	-0,23	558090
skywalking (incubating)	287505f3cf5bbb213de83cd54f76a5be	12361	29.04.2019 12:50	3,68	55624,5
hbase	b90a7ac3118f6ecfa4537f1ab359bc5b	136023	29.04.2019 12:35	1,58	446155,44
Kafka	7918f57464e4595884652054b12f1735	136970	29.04.2019 12:29	2,78	495831,4
Kafka	7918f57464e4595884652054b12f1735	136970	18.04.2019 17:54	2,23	398582,7
angular TS	b40909083e1a671f8a9a16f51a2fffa1	86183	18.04.2019 12:39	3,96	430915
three	56c65bdac0fd8f8797d3e0ae7bf261e0	1521	18.04.2019 12:39	1,9	7605
Visual Studio	59316bad6ca2c1874312a351ef418413	105533	18.04.2019 09:12	2,13	525554,34
three	56c65bdac0fd8f8797d3e0ae7bf261e0	1521	18.04.2019 09:11	1,87	7605
hbase	b90a7ac3118f6ecfa4537f1ab359bc5b	136023	16.04.2019 15:16	1,73	457037,28
skywalking (incubating)	287505f3cf5bbb213de83cd54f76a5be	12361	16.04.2019 15:15	3,68	55624,5
skywalking (incubating)	287505f3cf5bbb213de83cd54f76a5be	12361	16.04.2019 15:13	3,68	55624,5
hbase	b90a7ac3118f6ecfa4537f1ab359bc5b	136023	16.04.2019 15:02	2,07	433913,37
hbase	b90a7ac3118f6ecfa4537f1ab359bc5b	136023	12.04.2019 10:10	1,7	429832,68
skywalking (incubating)	287505f3cf5bbb213de83cd54f76a5be	12361	12.04.2019 10:06	2,29	55253,67
hbase	b90a7ac3118f6ecfa4537f1ab359bc5b	136023	12.04.2019 09:55	1,97	412149,69
skywalking (incubating)	287505f3cf5bbb213de83cd54f76a5be	12361	12.04.2019 09:54	2,29	55253,67
hivemall(incubating)	7a85ba7f87011d452677ec51686f0e2c	69258	12.04.2019 09:50	2,77	304735,2
skywalking (incubating)	287505f3cf5bbb213de83cd54f76a5be	12361	12.04.2019 09:45	2,91	53275,91
hivemall(incubating)	7a85ba7f87011d452677ec51686f0e2c	69258	12.04.2019 09:43	2,82	307505,52
skywalking (incubating)	287505f3cf5bbb213de83cd54f76a5be	12361	12.04.2019 09:39	1,92	38566,32
hivemall(incubating)	7a85ba7f87011d452677ec51686f0e2c	69258	12.04.2019 09:37	2,8	306120,36
skywalking (incubating)	287505f3cf5bbb213de83cd54f76a5be	12361	12.04.2019 09:19	2,95	54264,79
hivemall(incubating)	7a85ba7f87011d452677ec51686f0e2c	69258	12.04.2019 09:18	2,94	310275,84
lgv	f0498d40214513effa4c415c83a161c6	111618	10.04.2019 17:20	-0,23	558090
ol-cesium	b5b06cbae1ecb58dc86bcebcf99bb8bf	4911	10.04.2019 16:52	2,34	24555
lgv	f0498d40214513effa4c415c83a161c6	111618	10.04.2019 15:28	-0,31	558090
Kafka	7918f57464e4595884652054b12f1735	136970	09.04.2019 20:52	2,92	512267,8
Kafka	7918f57464e4595884652054b12f1735	136970	09.04.2019 20:33	2,24	399952,4

Then I have another Table for the projects and their consisting repos. Including sumloc what is the size of the complete project and repoloc what is the size of the referenced repo.

projectdetails.sumloc	projectdetails.projectId	projectdetails.repositoryCount	projectdetails.projectName	repositoryId	repositoryName	uid	projectdetails.repoloc
116529	2	2	GeoMaster	3	lgv	f0498d40214513effa4c415c83a161c6	111618
116529	2	2	GeoMaster	4	ol-cesium	b5b06cbae1ecb58dc86bcebcf99bb8bf	4911
136970	1	1	Kafka	1	Kafka	7918f57464e4595884652054b12f1735	136970
217642	3	3	Trends	8	hbase	b90a7ac3118f6ecfa4537f1ab359bc5b	136023
217642	3	3	Trends	5	hivemall(incubating)	7a85ba7f87011d452677ec51686f0e2c	69258
217642	3	3	Trends	6	skywalking (incubating)	287505f3cf5bbb213de83cd54f76a5be	12361

I have in repo.overallRating values between 5 and -5 for the repositories for different timestamps. 

Since I just get a new scan/timestamp if any changes are done in a repository I might only get a timestamp of some repo every x month, while the rest of the repositories are constantly creating timestamps.

Repo.loc is the size.

Repo.overall is the score.

I want to SUM(repo.overallRating*repo.loc)/projectdetailssumloc)  in each project. But it has to just take the last timestamps into account available. 

So that a project consisting of repos a, b and c the last timestamp of each of these repos is used in the formula. 

By this I get a weighted (after size) overall.rating for my projects that I can plot over a timeline, for trend analytics. 

Thank you for your help community. I am struggling with this since hours....

[Greg_Deckler]

Any chance you could post what your expected output would be from the data provided?

[Riddler]

@Greg_Deckler  Of course, my apologies.

In the meanwhile, I created a right join to connect project data with repo timestamp data.

So all fields you see here are available (more but that is just noise for this topic)

This would be the output for the Project named Trends

It is filtered on Trends and should ignore the other 4 projects and vice versa. For this I created the column 

repositories.

repo.uid+project.id

That is unique in each project, allowing two projects to use the same repositories. So

repositories.

repo.uid+project.id

we can use for a DISTINCT Filter and I guess we can use an EARLIER to get those numbers together. It is a accumulated Value like here https://community.powerbi.com/t5/Desktop/Accumulate-added-value/td-p/82382 but I need it to MAX it to the number of unique values of 

repositories.

repo.uid+project.id that are above it. 

Similar to this https://stackoverflow.com/questions/51353339/measure-to-sum-of-more-recent-values-using-dax-from-pow...

I wrote down the formular for each line in the RollingOverallRating in Excel (scroll right)

project details. projectId	project details. sumloc	project details. repository Count	project details. project Name	repositories. repo. repositoryName	repositories. repo.uid+project.id	repositories. repo.loc	repositories. repo. scantimestamp	repositories. repo.overallRating	Rolling Overallrating
3	227661	3	Trends	hivemall(incubating)	37a85ba7f87011d452677ec51686f0e2c	69258	12.04.2019 09:18	2,94	((2,94*69258))/(69258)
3	227661	3	Trends	skywalking (incubating)	3287505f3cf5bbb213de83cd54f76a5be	22380	12.04.2019 09:19	2,95	((2,94*69258)+(2,95*22380))/(69258+22380)
3	227661	3	Trends	hivemall(incubating)	37a85ba7f87011d452677ec51686f0e2c	69258	12.04.2019 09:37	2,8	((2,80*69258)+(2,95*22380))/(69258+22380)
3	227661	3	Trends	skywalking (incubating)	3287505f3cf5bbb213de83cd54f76a5be	22380	12.04.2019 09:39	1,92	((2,80*69258)+(1,92*22380))/(69258+22380)
3	227661	3	Trends	hivemall(incubating)	37a85ba7f87011d452677ec51686f0e2c	69258	12.04.2019 09:43	2,82	((2,82*69258)+(1,92*22380))/(69258+22380)
3	227661	3	Trends	skywalking (incubating)	3287505f3cf5bbb213de83cd54f76a5be	22380	12.04.2019 09:45	2,91	((2,82*69258)+(2,91*22380))/(69258+22380)
3	227661	3	Trends	hivemall(incubating)	37a85ba7f87011d452677ec51686f0e2c	69258	12.04.2019 09:50	2,77	2,652773522
3	227661	3	Trends	skywalking (incubating)	3287505f3cf5bbb213de83cd54f76a5be	22380	12.04.2019 09:54	2,29	((2,77*69258)+(2,29*22380))/(69258+22380)
3	227661	3	Trends	hbase	3b90a7ac3118f6ecfa4537f1ab359bc5b	136023	12.04.2019 09:55	1,97	((2,77*69258)+(2,29*22380)+(1,97*136023))/(69258+22380+136023)
3	227661	3	Trends	skywalking (incubating)	3287505f3cf5bbb213de83cd54f76a5be	22380	12.04.2019 10:06	2,29	((2,77*69258)+(2,29*22380)+(1,97*136023))/(227661)
3	227661	3	Trends	hbase	3b90a7ac3118f6ecfa4537f1ab359bc5b	136023	12.04.2019 10:10	1,7	((2,77*69258)+(2,29*22380)+(1,7*136023))/(227661)
3	227661	3	Trends	hbase	3b90a7ac3118f6ecfa4537f1ab359bc5b	136023	16.04.2019 15:02	2,07	((2,77*69258)+(2,29*22380)+(2,07*136023))/(227661)
3	227661	3	Trends	skywalking (incubating)	3287505f3cf5bbb213de83cd54f76a5be	22380	16.04.2019 15:13	3,68	((2,77*69258)+(3,68*22380)+(2,07*136023))/(227661)
3	227661	3	Trends	skywalking (incubating)	3287505f3cf5bbb213de83cd54f76a5be	22380	16.04.2019 15:15	3,68	((2,77*69258)+(3,68*22380)+(2,07*136023))/(227661)
3	227661	3	Trends	hbase	3b90a7ac3118f6ecfa4537f1ab359bc5b	136023	16.04.2019 15:16	1,73	((2,77*69258)+(3,68*22380)+(1,73*136023))/(227661)
3	227661	3	Trends	hbase	3b90a7ac3118f6ecfa4537f1ab359bc5b	136023	29.04.2019 12:35	1,58	((2,77*69258)+(3,68*22380)+(1,58*136023))/(227661)
3	227661	3	Trends	skywalking (incubating)	3287505f3cf5bbb213de83cd54f76a5be	22380	29.04.2019 12:50	3,68	2,14845494
3	227661	3	Trends	skywalking (incubating)	3287505f3cf5bbb213de83cd54f76a5be	22380	29.04.2019 18:05	3,57	2,137641493
3	227661	3	Trends	skywalking (incubating)	3287505f3cf5bbb213de83cd54f76a5be	22380	30.04.2019 09:56	3,63	2,143539737

The unfiltered table has of course more projects. But to much chars for this post.

*edit, table had been a row off

Thanks in advance!

Greets