# Change of price - data model

#### didijaba

##### Well-known Member
Hello,
I am new to power thing so pls be patient with me . I have large data set with transaction records of buying various products in different stores. Each record has time of transaction. What I need is to see when price for product in each store changed. I do not know how to do this. I have 650000+ rows of record. Thanks in advance for any advice how to solve this.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### didijaba

##### Well-known Member
I was thinking formula should be something like GROUP by SITE, GROUP by PRODUCT, SORT SaleTime ascending, and then if PRICE different then PRICE record before show it, and if PRICE equals record before remove it.

#### scottsen

##### Well-known Member
Can you tell me a bit more about your scenario? Like, how will you view/use this info?

You will... have all products on a slicer, and when you select one it will show all the prices for that product, along with the date it changed?

#### Tianbas

##### Board Regular
I would create 3 calculated columns for this

1) date of last transaction
=calculate(max(Table7[Date]),filter(table7,table7[Store]=EARLIER(table7[store]) && table7[product]=EARLIER(Table7[Product]) && Table7[Date])))<earlier(table7[date])))

2) price of last transaction
=calculate(max(Table7[Price]),filter(table7,table7[Store]=EARLIER(table7[store]) && table7[product]=EARLIER(Table7[Product]) && Table7[Date]=EARLIER(Table7[date of last transaction])))

3) Price has changed
=if(Table7[Price]=Table7[Price of last transaction],"","NEW PRICE")</earlier(table7[date])))

#### didijaba

##### Well-known Member
Thanks for your anawers, I used power query and sorted table ascending (site, brand, saletime). Then I loaded query and using vba copied table and removed rows I do not need. I will post macro later. But I need better solution, data source has cca 10 mil. records.

Last edited:

#### didijaba

##### Well-known Member
I can provide file if it helps.

#### didijaba

##### Well-known Member
I would create 3 calculated columns for this

1) date of last transaction
=calculate(max(Table7[Date]),filter(table7,table7[Store]=EARLIER(table7[store]) && table7[product]=EARLIER(Table7[Product]) && Table7[Date])))<earlier(table7[date])))

2) price of last transaction
=calculate(max(Table7[Price]),filter(table7,table7[Store]=EARLIER(table7[store]) && table7[product]=EARLIER(Table7[Product]) && Table7[Date]=EARLIER(Table7[date of last transaction])))

3) Price has changed
=if(Table7[Price]=Table7[Price of last transaction],"","NEW PRICE")</earlier(table7[date])))
Thanks, I will try this when I come home from work.

#### didijaba

##### Well-known Member
Code:
``````Sub Macro1()
Dim Row As Long
Dim lastrow As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For Row = 3 To lastrow 'heading row is first row
'first column is SITE, second is PRODUCT, and fourth is PRICE
If Cells(Row, 1).Value = Cells(Row - 1, 1).Value And Cells(Row, 2).Value = Cells(Row - 1, 2) And Cells(Row, 4).Value = Cells(Row - 1, 4) Then
ActiveSheet.Cells(Row, 7).Value = 1 'later I filter values that are blank (no 1) and paste them to new sheet
End If
Next Row
End Sub``````

#### scottsen

##### Well-known Member
Assuming you can find something that works for you... If you are using Excel 2013, this is probably a good place to use a table query, see here for steps: DAX Table Query Example | Tiny Lizard

Replies
0
Views
234
Replies
0
Views
221
Replies
2
Views
851
Replies
2
Views
615
Replies
2
Views
420

1,195,635
Messages
6,010,834
Members
441,569
Latest member
PeggyLee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back