topic Re: Average from every day in DAX Commands and Tips

Average from every day

Pan_Forex — Fri, 20 Jan 2023 13:08:22 GMT

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

Re: Average from every day

amitchandak — Fri, 20 Jan 2023 13:26:59 GMT

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])))

Re: Average from every day

Pan_Forex — Fri, 20 Jan 2023 13:55:27 GMT

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.

Re: Average from every day

tamerj1 — Fri, 20 Jan 2023 15:07:04 GMT

Hi Pan_Forex

please try

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

Re: Average from every day

Pan_Forex — Fri, 20 Jan 2023 15:19:41 GMT

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?

Re: Average from every day

tamerj1 — Fri, 20 Jan 2023 15:31:21 GMT

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] )
)
)

Re: Average from every day

FreemanZ — Sat, 21 Jan 2023 06:57:35 GMT

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 ) )