Compare price previous non continuous date

[josfebo]

Hello to all of you!

After reading many forums and posts related to this topic, I need your help.

I am building a table to compare the price of a product of the current date against the price of the product of the immediately previous date (the dates are not continuous). I have managed to do this, the problem is when the product is retired from one date to another, as I can't get the value of the price of the previous month. The aim of this is precisely to detect that if on the current date there is no price but on the previous one there was, to indicate that the product has been removed.

Attached is an image of the table as well as the DAX measure I have constructed to obtain the previous price.

I am grateful for any help!

[Anonymous]

Hi, Just to clarify, you have tables with product price keeps on update as per requirement. 

What i see from here is that your table is in good condition, but only on how you want to populate your data.

If below data populate is best for you, you can use MAX date function to get latest price.

Latest Price:

Product	Price	Last updated price
A	2.5	01/01/22
B	1.6	14/03/22
C	3.4	14/02/22
D	5.01	25/04/22
E	10	01/01/22

If you still want your historical table format, use the same method on MAX function of date.

Create a Measure:  IF NULL , Max Date , Get value.

[Ashish_Mathur]

Hi,

In the row which has the blue circles, what answer would you expect for 22/4/2022?  Should the previous price as on this date be nil or 1.20?

[Ashish_Mathur]

Hi,

Your answers to Parry2K and me are completely different. 

[josfebo]

Yes it should be 1.20 for 22/04/2022. Although the product doesn't exist 22/04/2022, it should get the price of previous month. Thank you for reply!

[parry2k]

@josfebo check if this video on my YT channel helps. https://youtu.be/CuSBPOF66NA

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[josfebo]

Thank you very much for your help! It works perfectly well! However, there is a small error, because for later months, even if the previous month has no price, it takes the price of the last month that has information.

[Anonymous · Accepted solution]

Hi @josfebo ,

Please update the formula of your measure [Previous Day Price 1] as below and check whether it can return the correct result...

Previous Day Price 1 =
VAR current_date =
    SELECTEDVALUE ( d_Calendario[Fecha] )
VAR curresta =
    SELECTEDVALUE ( delivery_menu[Restaurante] )
VAR curitem =
    SELECTEDVALUE ( delivery_menu[xxcolumname] )
VAR previous_visibleDate =
    CALCULATE (
        MAX ( delivery_menu[Fecha] ),
        FILTER (
            ALLSELECTED ( delivery_menu ),
            delivery_menu[Restaurante] = curresta
                && delivery_menu[xxcolumname] = curitem
                && delivery_menu[Fecha] < current_date
                && [Precio] <> BLANK ()
        )
    )
VAR previous_visibleDatePrice =
    CALCULATE (
        [Precio],
        FILTER (
            ALLSELECTED ( delivery_menu ),
            delivery_menu[Restaurante] = curresta
                && delivery_menu[xxcolumname] = curitem
                && delivery_menu[Fecha] = previous_visibleDate
        )
    )
RETURN
    previous_visibleDatePrice

In addition, you can refer the following links to get it.

Power Query method:

Get previous row in Power Query - EXPLAINED

DAX method:

Power BI DAX Getting the Value of Previous Non-NULL Row

Calculate previous row using DAX

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards

[parry2k]

@josfebo what is your expectation in that case?

[amitchandak]

@josfebo , refer if these examples can help

Last Day Month Continuous = CALCULATE([sales],filter(ALL('Date'), eomonth('Date'[Date],0) = eomonth(MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date]),0)))

or

Last Day Month Continuous = CALCULATE([sales],filter(ALL('Date'), eomonth('Date'[Date],0) = eomonth(Calculate(max(Table[Date]), FILTER(ALL('Date'),'Date'[Date]<max('Date'[Date]))),0)))

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

[josfebo]

Thank you very much for your help! I have tried the solution you mention but the previous value returned is not correct. I guess it could work but I would have to dig more into the value it returns.