sum duplicate dates
1 Topic
Creating a (set of) measure(s) to sum values with a duplicate date, then apply rolling average
Hi, For context, I run a small game development studio with some friends. We have a team of volunteers we are looking to reimburse for their time. We have created a plugin that logs users' interactions and commands per world they work on. I am currently working on a tool that generates activity reports which will be used to determine how active (activity factor) someone was which in turn will calculate their final pay. I have got something half-decent set up using a date-table, some simple DAX functions, etc. In our data tables, interactions are logged per date, per world. This could look something like this: Date World Staff member Interactions 25-03-2021 Cave Tom 1452 25-03-2021 Overworld Tom 334 25-03-2021 Hell Roger 4782 26-03-2021 Cave Tom 245 26-03-2021 Overworld Ben 24962 The activity factor calculation is as follows: (3-month moving average of interactions per staff member)/(team average of interactions in calendar year) * 100 I am using a slicer to achieve this per staff member. The problem I am having is that the rolling average takes an average across all rows which is skewing the results. For example, the average for Tom in the example above would be (1452 + 334 + 245) / 3 = 677 without summing, ((1452 + 334) + 245) / 2 = 1015.5 with. Link to file: https://we.tl/t-2WG7G4YzzW I have done research on the issue but am completely stumped - I hope you can help me! Let me know if you need any further info. Best, GuusSolved1.3KViews0likes5Comments