Calculated Column - Deviation from Baseline on a daily basis

[rgadbois]

Hi, 

I have a large PBIX with information for a full team of athletes. Each day, we complete testing that provides me with multiple different metrics. My data for each athlete populates back to last season, in early 2021. I have created a measure that creates a baseline, set to a specific date. This works fine on visuals when filtered by the athlete, but I want to create a calculated column that does this on a daily basis to be tracked over time. The issue I run into is because the Column "Name" has numerous names in it, which repeat constantly, as the information runs horizontal, ie each column is the quality of what the information below it contains: Name, Date, Jump Height, etc. Could someone please provide direction on how to go about this? I have tried Filter, Calculate all with no evail. I am really strugglign to complete this. If I am able to provide a copy of the files, I am happy to do so. Thank you!

[v-yueyunzh-msft]

Hi , @rgadbois 

According to your description , you want to group by [Name] and [Date] fields to calculate the average of the [RSI modified Jump m/s] field. Right?

You can filter the data you want then you can calculate the average:

Column =
VAR _name = 'CMJ'[Name]
VAR _date = 'CMJ'[Date]
RETURN
    AVERAGEX (
        FILTER ( 'CMJ', 'CMJ'[Name] = _name && 'CMJ'[Date] = _date ),
        [RSI modified Jump m/s]
    )

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

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

[HoangHugo · Accepted solution]

Hi, try this one

Avarage_column = AVERAGEX(CALCULATETABLE(CMJ,[Date]= EARLIER[Date],[Name]=EARLIER[Name]), [RSI modified Jump m/s])

It can take long time, if data is large