Creating a (set of) measure(s) to sum values with a duplicate date, then apply rolling average

[Anonymous]

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,

Guus

[daxer-almighty]

Hi @Anonymous 

Would you please mark one of the posts as the answer. Kudos would be appreciated but not required, of course. 

Thanks.

[Anonymous · Accepted solution]

Hi @Anonymous 

Sorry to say that but your formulas are wrong. Using the time-intel functions on a table that is not a date table leads to wrong calculations. Please create a correct model first (star schema), then use a date table in the model. If you don't do it, you'll be calculating wrong numbers before you know it and you will not even be aware they are incorrect. Save yourself the grief.

Start with this article: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

[Anonymous]

@Anonymous 

Thanks, it's definitely set me on a path to understand data modelling/SQL/relationships better. I managed to get it to work in the end.

[Anonymous]

Hi there, @Anonymous.

Can you show me your measure(s)? I can't view the file due to my company's policy. A 3-month rolling average is such a common calculation that I'm really surprised that you can't get this right. Let me see what you've got.

By the way, I'd also like to have at least rough idea about your model since how you write DAX depends on the model. On top of that, the average a formula will be calculating will depend on the slicers in place. If you, for instance, don't slice by 'world' the average will not take worlds into consideration; it'll be oblivious to them. But if you group by worlds, the average will be calculated for each world separately. Same with other dimensions... This is how formulas work - they are aware of the slicing and dicing. I know you're aware of this and it's the reason I'm surprised you can't get this right.

Thanks.

[Anonymous]

Hi Daxer,

Thanks for your reply. I'm not struggling to get a moving average to work - that part is working fine. It's combining a moving average with summing all values with the same date and then applying a moving average.

At this point I don't even know whether it would be best to tackle this with data modelling or create a measure that can do both simultaneously.

Measures as requested:

Moving average per staff member

Blocks - Moving Avg 3 Months = 
CALCULATE (
    AVERAGEX ( 'worldinteractions', 'worldinteractions'[ActionAmount] ),
    DATESINPERIOD (
        worldinteractions[Date],
        LASTDATE ( worldinteractions[Date] ),
        -3,
        MONTH
    )
)

Team average in current calendar year

Blocks - Interactions current year = 
CALCULATE(
    CALCULATE (
        AVERAGEX ( 'worldinteractions', 'worldinteractions'[ActionAmount] ),
        DATESINPERIOD('worldinteractions'[Date],LASTDATE('worldinteractions'[Date]),-1,YEAR)
    ),
REMOVEFILTERS(builders[Name]
))

 I suspect that the file might show some more insight as to what I'm trying to achieve but I hope the above paints a clearer picture. Let me know if you need any more info.