Needing a measure for last sales price

[Kaizu80]

Hi!

I want to make a price variance index to our Sales team. The data looks like this (Date-Code-Price):

InvoiceDate	ItemCode	UnitPrice
2.12.2016	1699	150		Last price
31.1.2016	1948	151,82
29.2.2016	1948	139,98
31.3.2016	1948	128,07
30.4.2016	1948	162,51
31.5.2016	1948	208,29
30.6.2016	1948	240,73
31.7.2016	1948	178,91
31.8.2016	1948	168,04
30.9.2016	1948	170,03
30.11.2016	1948	170,03
31.1.2017	1948	234,05		Last price
31.1.2016	1949	137,58
29.2.2016	1949	126,85
31.3.2016	1949	116,07
30.4.2016	1949	147,28
31.5.2016	1949	188,76
30.6.2016	1949	218,16
31.7.2016	1949	162,14
31.8.2016	1949	152,29
30.9.2016	1949	154,09
31.10.2016	1949	147,66
30.11.2016	1949	154,09
31.12.2016	1949	195,47
31.1.2017	1949	212,1
28.2.2017	1949	210,01
31.3.2017	1949	192,65
30.4.2017	1949	214,7
31.5.2017	1949	201,89
1.6.2017	1949	201,89
1.6.2017	1949	194,93
30.6.2017	1949	188,8
31.7.2017	1949	189,76		Last price
29.4.2016	1995	-380,4
27.5.2016	1995	150
17.6.2016	1995	120
29.6.2016	1995	50
18.8.2016	1995	50
31.8.2016	1995	50
16.9.2016	1995	150		Last price
15.2.2016	1999	345
31.3.2016	1999	-4171,5
15.6.2016	1999	-1217,24
16.6.2016	1999	-150
23.8.2016	1999	-150
18.11.2016	1999	-800
22.12.2016	1999	-3100
20.4.2017	1999	11,9
16.6.2017	1999	523		Last price

How do I make a Measure or edit the data in the query editor so that I would be able to pick the latest sales price per item from the whole data? I do not want to delete anything since I want to compare the latest price to our average price.

BR

Kaj

[Anonymous]

Hi,

How would measure be modified to include last price based on customers and not just product code? I have multiple customers who buy the same product code and would like to be able to see last price for each customer for each product item.

[Anonymous]

I have a very similar dataset but I'm getting the following error:

"A single value for variaton 'Date' for column 'Date' in table 'GRVTrans' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

[v-sihou-msft · Accepted solution]

@Kaizu80

You can create measure like below:

Last Price = 
var maxDate=CALCULATE(MAX(Table2[Date]),ALLEXCEPT(Table2,Table2[Item]))
return
CALCULATE(SUM(Table2[Price]),FILTER(Table2,Table2[Date]=maxDate))

Regards,

[Kaizu80]

@v-sihou-msft Thank you! This works good! Although I had to change the Price calculation from Sum to Average due to our not so accurate data 🙂

[Joschko]

Hi,

create a column sequence:

Sequence = VAR InvDate = Sales[InvoiceDate]

RETURN

COUNTROWS(Filter(CALCULATETABLE(Sales;ALLEXCEPT(Sales;Sales[ItemCode]));Sales[InvoiceDate]>=InvDate))

You will get a 1 for the latest entry per ItemCode. A 2 for the second latest entry,.. and so on.

HTH

Jörg