topic Re: DAX to get the last purchase price in DAX Commands and Tips

DAX to get the last purchase price

p_rathinavel — Tue, 24 May 2022 14:23:41 GMT

Hi All,

I’m crashing my head to create a DAX to get the price of an item from the previous purchase dates. Whenever, the price is zero or not available it should lookup for the last purchase price.

For example,

Date	Item	Price
31/05/2022	ITEM1	0
31/04/2022	ITEM1	5
31/03/2022	ITEM1	10
31/02/2022	ITEM1	2
31/01/2022	ITEM1	3

In this case, as the ITEM 1 doesn’t have the price for 31/05/2022, then it should take the last purchase price on 31/04/2022.

Likewise, in the below example, ITEM 1 doesn’t have the price for 31/03/2022 then it should look up for the price on 31/01/2022.

Date	Item	Price
31/05/2022	ITEM1	0
31/04/2022	ITEM1	5
31/03/2022	ITEM1	0
31/02/2022	ITEM1	0
31/01/2022	ITEM1	3

Like wise, the missing price has to be filled by the last purchase price by date.

looking for the community help amitchandak thanks !

Re: DAX to get the last purchase price

p_rathinavel — Tue, 24 May 2022 14:31:01 GMT

I missed something, if the price is not zero or blank then it should give the actual price and if it is zero or blank it should look up for the price until it finds a value. thanks

Re: DAX to get the last purchase price

p_rathinavel — Tue, 24 May 2022 14:33:02 GMT

Greg_Deckler SpartaBI Jihwan_Kim

Re: DAX to get the last purchase price

Whitewater100 — Tue, 24 May 2022 15:17:47 GMT

Hi:

Please see solution attached via link. I put your answer on page two. There is a date table added. There is another new measure named [price].

File for review - pg 2 link below

https://drive.google.com/file/d/18v8uT58lmHrsilqyrDLLR9YOEWdr45Kl/view?usp=sharing

Re: DAX to get the last purchase price

Whitewater100 — Tue, 24 May 2022 16:46:57 GMT

Hi Again:

Here is another solution with just one meausue, same concept. You still would need a Date table which is best practice.

Please mark as solution if this is what you are looking for. Thanks!!

Last Price (Combine Measures) =

CALCULATE( LASTNONBLANKVALUE(DISTINCT(Dates[Date]), Calculate(SUM(Prices[Price]),Prices[Count Me] = 1)))

Re: DAX to get the last purchase price

p_rathinavel — Tue, 24 May 2022 18:08:04 GMT

HI Whitewater100 thanks for your quick turnaroun !

the solution is partially corret but I would like to use the last price for the date and item which has the price as zero/blank.

Please check the below table in which the last price is assigned to the date and item which has the missing price i.e. zero. and there are many items and dates in my table, so please imagine the scenario.

thanks waititing for your solution !!

Re: DAX to get the last purchase price

Whitewater100 — Tue, 24 May 2022 19:49:21 GMT

HI:

OK, I put index column in table. Now easier to go back.

https://drive.google.com/file/d/18v8uT58lmHrsilqyrDLLR9YOEWdr45Kl/view?usp=sharing

You don't need all these columns but they break down getting to result. Hope this solves it now.

Re: DAX to get the last purchase price

Icey — Fri, 27 May 2022 03:23:59 GMT

Hi p_rathinavel ,

How about this?

If you want a column, try this:

Last Price Column = VAR PreDate_ = CALCULATE ( MAX ( Prices[Date] ), FILTER ( ALLEXCEPT ( Prices, Prices[ITEM1] ), Prices[Date] < EARLIER ( Prices[Date] ) ) ) RETURN IF ( Prices[Price] > 0, Prices[Price], CALCULATE ( MIN ( Prices[Price] ), FILTER ( ALLEXCEPT ( Prices, Prices[ITEM1] ), Prices[Date] = PreDate_ ) ) )

If you want a measure, try this:

Last Price Measure = VAR PreDate_ = CALCULATE ( MAX ( Prices[Date] ), FILTER ( ALLEXCEPT ( Prices, Prices[ITEM1] ), Prices[Date] < MAX ( Prices[Date] ) ) ) RETURN IF ( MIN ( Prices[Price] ) > 0, MIN ( Prices[Price] ), CALCULATE ( MIN ( Prices[Price] ), FILTER ( ALLEXCEPT ( Prices, Prices[ITEM1] ), Prices[Date] = PreDate_ ) ) )

Best Regards,

Icey

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