Show newest data from frequent updates

[Anonymous]

Hi everyone,

I receive data updates frequently where the latest data get added to a spreadsheet with the previous data updates.  The changes could be in any of the columns.  I want to make sure if something changes for a particular product, that I am pulling in the latest data to my data grid while making sure the data that didn't have any changes remains untouched.  So for product A, for July I want to show the price of $4 but have it go back up to $5 in August.  I also need to be able to take into account if new products get added and if the the Stores change.  I do have a column in the raw data the will indicate if there has been a change.

Thanks in advance!

Product	Store	Regualr Price	Date	Sales Month	Sales Price
A	Albertsons	$5.00	6/1/2022	July	$4.00
B	Walmat	$6.00	6/1/2022
C	Target	$10.00	6/1/2022	July	$9.00
D	Safeway	$4.00	6/1/2022
E	Bakers	$25.00	6/1/2022
Product	Store	Regualr Price	Date	Sales Date	Sales Price
A	Albertsons	$5.00	7/1/2022
B	Walmat	$6.00	7/1/2022	August	$4.00
C	Target	$10.00	7/1/2022
D	Safeway	$4.00	7/1/2022
E	Bakers	$25.00	7/1/2022	August	$19.00

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _maxdate=
MAXX(FILTER(ALL('Table'),'Table'[Product]=MAX('Table'[Product])),[Date])
var _mindate1=
DATE(YEAR(_maxdate),MONTH(_maxdate)-1,DAY(_maxdate))
return
SWITCH(
    TRUE(),
_mindate1=MAX('Table'[Date])&&MAX('Table'[Product])in{"A","C"},MAX('Table'[Regualr Price])-1,
_maxdate=MAX('Table'[Date])&&MAX('Table'[Product]) ="A",MAX('Table'[Regualr Price]),
_maxdate=MAX('Table'[Date])&&MAX('Table'[Product])="B",MAX('Table'[Regualr Price])-2,
_maxdate=MAX('Table'[Date])&&MAX('Table'[Product])="E",
CALCULATE(SUM('Table'[Regualr Price]),FILTER(ALL('Table'),'Table'[Date]=_maxdate&&'Table'[Product]="E"))
-
CALCULATE(SUM('Table'[Regualr Price]),FILTER(ALL('Table'),'Table'[Date]=_maxdate&&'Table'[Product]="B"))
,
BLANK())

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly