Capture price changes

[Anonymous]

Hi,

I have below data, I would like to capture the changes of the price for each Part No:

I would like the result to be like below, capture the price whenever it changes:

can someone help please?

[v-diye-msft]

Hi @Anonymous 

you can do this by a easy function:

[Anonymous]

@v-diye-msft is Ealiest Date a calculated column? can you please share the DAX.

[v-diye-msft]

Hi @Anonymous 

I didn't write DAX, just change the aggreate type of date column, you'll make it.

[Anonymous]

@v-diye-msft thanks , I will try it.

[Anonymous]

@v-diye-msft , below 2 tables are the row data in table 1 and aggregated in table 2 for a particulare part number, it gives the correct unique price, but the earliest dates in table 2 are wrong, it looks like it is getting the earliest date regardless of the part number.

Also can you please advise how can I add the sum of the distinct prices, for below example the total sum is 31.23

[Ashish_Mathur]

Hi,

One way could be that in the Query Editor, you may select all columns and then right click > Remove Duplicates.

Hope this helps.

[Anonymous]

@Ashish_Mathur , my report is connected to the tabular model, I can't use query editor.

Thanks for your comment anyway.

[Fowmy]

@Anonymous 

Can you try this  as a new column to your table and check

Column = 

VAR _PRICE = MAXX(FILTER('Table', 'Table'[Part No] = EARLIER('Table'[Part No]) && 'Table'[Date] < EARLIER('Table'[Date])), 'Table'[Pirce])
VAR _PART= MAXX(FILTER('Table', 'Table'[Part No] = EARLIER('Table'[Part No]) && 'Table'[Date] < EARLIER('Table'[Date])), 'Table'[Part No])
VAR CP = [Pirce]

RETURN

IF(           
    [Part No] = _PART ||( _PART=BLANK() && _PART <> BLANK()),
    [Pirce] , 
    BLANK() 
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Thanks @Fowmy , my data source is a tabular model, and the source table is very large, around 4 millions record, so I was trying to avoid adding calcualted column.

Is there away to just do it in a measure or measures?

[Anonymous]

One thing I forgot to menation that date column is from another table, but it is join with the part table.