Average from every day

[Pan_Forex]

Hi guys! I wonder if it is possible to create a table that adds new rows every day and leaves the old ones unchanged. I have a table that is updated several times every day with new rows and a second table that counts the average for each player. 

Date	ID	Player	Score
20.01.2023	1025	A	8
20.01.2023	1025	B	4
20.01.2023	1025	C	5
20.01.2023	1025	D	7
18.01.2023	1020	A	6
18.01.2023	1020	B	5
18.01.2023	1020	C	5
18.01.2023	1020	D	5
18.01.2023	1020	E	4
18.01.2023	1020	F	6
17.01.2023	958	F	5
17.01.2023	958	C	6
17.01.2023	958	D	5
17.01.2023	958	E	3

Now it looks like this:

Player	Average Score
A	7
B	4.5
C	5.33
D	5.66
E	3.5
F	5.5

I would like the new table to count such a ranking from each day. It would add new updated scores every day, while leaving the old scores unchanged:

Date	Player	Average Score
20.01.2023	A	7
20.01.2023	B	4.5
20.01.2023	C	5.33
20.01.2023	D	5.66
18.01.2023	A	6
18.01.2023	B	5
18.01.2023	C	5.5
18.01.2023	D	5
18.01.2023	E	3.5
18.01.2023	F	5.5
17.01.2023	F	5
17.01.2023	C	6
17.01.2023	D	5
17.01.2023	E	3

[tamerj1 · Accepted solution]

@Pan_Forex 

Please try

New Table =
ADDCOLUMNS (
SUMMARIZE ( 'Table', [Date], 'Table'[Player] ),
"Average Score",
CALCULATE (
AVERAGE ( 'Table'[Score] ),
ALLEXCEPT ( 'Table', 'Table'[Player] ),
[Date] <= EARLIER ( [Date] )
)
)

[FreemanZ · Accepted solution]

hi @Pan_Forex 

this shall also work:

Table = 
ADDCOLUMNS(
    SUMMARIZE(
        TableName,
        TableName[Date],
        TableName[Player]
    ),    
    "Avg",
    VAR _date=TableName[Date]
    RETURN CALCULATE(
    AVERAGE(TableName[Score]),
    TableName[Date]<=_date
    )
)

[tamerj1]

Hi @Pan_Forex 

please try

Average Score =
CALCULATE (
AVERAGE ( 'Table'[Score] ),
ALLEXCEPT ( 'Table', 'Table'[Player] ),
[Date] <= MAX ( [Date] )
)

[Pan_Forex]

thank you for your reply. Your solution works but I would still like to have this data in a new table and not as a measure. Is this possible? 

[tamerj1 · Accepted solution]

@Pan_Forex 

Please try

New Table =
ADDCOLUMNS (
SUMMARIZE ( 'Table', [Date], 'Table'[Player] ),
"Average Score",
CALCULATE (
AVERAGE ( 'Table'[Score] ),
ALLEXCEPT ( 'Table', 'Table'[Player] ),
[Date] <= EARLIER ( [Date] )
)
)

[amitchandak]

@Pan_Forex , try a measure with help from the date table

Cumm Sales = CALCULATE(AverageX(Values('Date'[Date]), CALCULATE(SUM(Table[Score]))) ,filter(all('Date'),'Date'[date] <=max('Date'[date])))

[Pan_Forex]

Thank you for your reply. I would like the average to be calculated for each player separately and not all together from each day, and to create a new table with results only.