Show average linked on last date

[Anonymous]

Hi guys,

simple, straightforward question. I have the following question:

Customer	Product	Sales	SellDate
A	A	300	7/10/2018
A	A	400	5/10/2018
B	A	200	3/10/2018
B	A	300	1/10/2018

I have calculated an average per customer. For example: Customer A has an average of (300 + 400) / 2 = 350 and B has (200/300) / 2 = 250.

Now I want to show the average linked to the last Selldate:

Customer	Product	AvgSales	Date
A	A	350	7/10/2018
B	A	250	3/10/2018

Anyone some smart idea how to make linkage ?

[Vvelarde]

@Anonymous

Hi, this is that you want?

 Image

[Anonymous]

Hi Vvelarde,

thanks a lot for your reply too but that's not exactly what I am looking for.

I need it calculated to perform further actions.

The main goal in the end is to calculate the average of sellings of each customer based on the last selldate and the corresponding quarter. 

Customer	Product	Sales	Date
A	A	350	7/10/2018
B	A	250	3/10/2018
		300	Q4 2018

A dynamic chart will result with the average sellings per quarter when I link the date with my DimDate table.

Therefore I need to calculate the linkage. 😞 Sounds complicated, is complicated. 

[ryan_mayu]

@Anonymous

I created a new table to get this result. Does this work for you?

table = SUMMARIZE(Sheet18,Sheet18[customer],Sheet18[product],"value",AVERAGE(Sheet18[sales]),"date",MAX('Sheet18'[selldate]))

[Anonymous]

Hi ryan,

that works perfect but would it possible to add it to the current table like that ?

Customer	Product	Sales	Date	Avg Sales	AvgDate
A	A	300	7/10/2018	350	7/10/2018
A	A	400	5/10/2018	350	7/10/2018
B	A	200	3/10/2018	250	3/10/2018
B	A	300	1/10/2018	250	3/10/2018

[ryan_mayu · Accepted solution]

@Anonymous

Please try the coding below. Thanks

average = CALCULATE(SUM('Sheet18'[sales]),FILTER(Sheet18,Sheet18[customer]=EARLIER(Sheet18[customer])))

date = MAXX(FILTER(Sheet18,Sheet18[customer]=EARLIER(Sheet18[customer])),'Sheet18'[selldate])