Sort the report based on the date key,item key and loc key then apply the filter by top 2 then only the changed records will be available on the report.
Try to create these measures:
"min_amount" = MIN(amount)
"Orignal_Amt" = AGGREGATE(min_amount AT item_key,loc_key)
"max_amount" = MAX(amount)
"Orignal_Amt" = AGGREGATE(max_amount AT item_key,loc_key)
Also create a COUNT() base on item_key,loc_key and filter it by > 1
Does this approach with comparing prices in the first and last date of the period actually indicates the price change?
What about the sample data below - is considered to be a price change or not?
Item_key Loc_key Date_key Reason_Code Amount Load_Date
2 2 20131110 0 325 12/11/2013
2 2 20131111 0 1100 12/11/2013
2 2 20131112 0 1200 12/11/2013
2 2 20131113 3 325 13/11/2013
Use 2 requests:
Req1 selects surrogate key for the first and last record by load date for each location and product. Surrogate key is just a concatenated expression of Item_Key, Loc_Key and Load_Date fields (use separators between them).
For this request you need to filter out all other dates but first and last - use Rank function with BY clause. It will be 2 calculated fields: RANK(Date_Load BY Item_Key, Loc_Key) = last date, RANK(-Date_Load BY Item_Key, Loc_Key) = first date. Select records having any of these ranks=1.
In result this request should return up to 2 records for each product-location combination with surrogate key, something like this for my example:
Item_key Loc_key Date_key Reason_Code Amount Load_Date KEY
2 2 20131110 0 325 12/11/2013 LOC:2;ITM:2;DT:20131110
2 2 20131113 3 325 13/11/2013 LOC:2;ITM:2;DT:20131113
In fact, it could be more than 2 records in case there are more than 1 change in a day for the price and no time stored on date field. This is up to you how to eliminate duplicates.
Now you have another request, resulting one, which is filtered by Req1 using the same surrogate key expression. Orignal_Amt =Min(Amount), Change_Amt = Max(Amount). Apply a filter to find records with different values between them.
In result, if product price is the same in the beginning and end of period (or it is single record) - these records will be filtered out, otherwise you get them in the report.
Play around with logical SQL under Advanced tab, it allows you to write requests with joins (as regular SQL does).
This is sort of desperate option as such an analysis is hard to maintain (make modifications to it).