Change of price - data model

didijaba

Well-known Member
Joined
Nov 26, 2006
Messages
511
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
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?
 
Upvote 0
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])))
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top