DAX to get the last purchase price

[p_rathinavel]

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 !

[Whitewater100]

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)))

[p_rathinavel]

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 !!

[Icey · Accepted solution]

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.

[Whitewater100]

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.

[Whitewater100]

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 

[p_rathinavel]

 

@Greg_Deckler @SpartaBI @Jihwan_Kim 

[p_rathinavel]

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