Calculating Dax upgrade or downgrade

[alialsayer]

If I have four given columns

1. Date
2. Customer ID
3. ProID
4. Price ($)

Each ProID represents a sequence of consecutive days and the price ($) is a subscription might vary during ProID as per customer selection based on this I need to calculate

1-Latest Price ($) per ProID:  will pick the latest price the customer selected at the same ProID at the maximum date

2- status: will compare current Price ($) value with previous Price ($) value

• If current Price ($) value > previous Price ($) value  then status will be Upgrade
• If the current Price ($) value = previous Price ($) value  then the status will be neutral
• If the current Price ($) value > the previous Price ($) value  then the status will be Downgrade
• Else null

3- ProdID status : will compare Latest Price ($) per ProID

• If the current Latest Price ($) per ProID value > the previous Latest Price ($) per ProID value  then the ProdID status will be Upgrade
• If the current Latest Price ($) per ProID value = the previous Latest Price ($) per ProID value  then the ProdID status will be neutral
• If the current Latest Price ($) per ProID value > the previous Latest Price ($) per ProID value  then the ProdID status will be Downgrade
• If the previous Latest Price ($) per ProID value is Null then ProdID status will be
• price customer selected at the same ProID at maximum date > price customer selected at the same ProID at minimum date then the ProdID status will be Upgrade
• price customer selected at the same ProID at maximum date = price customer selected at the same ProID at minimum date then the ProdID status will be neutral
• price customer selected at the same ProID at maximum date < price customer selected at the same ProID at minimum date then the ProdID status will be Downgrade

please download the below file :

Power Bi files 

[speedramps · Accepted solution]

Thanks alialsayer for the example input date and example desired output.

I will help you on a few conditions:-

1) Please format your input data correctly.  

I am unpaid volunteer who has proper job but gives some of my spare time to teach you Power BI for free. I wont format data like d/m/yyyy to dd/mm/yyyy.  You should do that youself  

2) I problem per ticket please. You have added at least 3 problems which is an entire project, not a how do I do this/that question. 

Here is an answer to problem 1-Latest Price ($) per ProID: 

Please click thumbs up and accept it and then raise new tickets with the corrected data and quote @speedramps in the ticket text. I will receive an automated email and help you. Thank you

Click here to download a solution 

Last price =

VAR maxdate =
CALCULATE(
MAX(Facts[Date]),
ALLEXCEPT(Facts,Facts[Customer ID],Facts[ProID]))

RETURN

CALCULATE(

    SELECTEDVALUE(Facts[Price ($)]),

    ALLEXCEPT(Facts,Facts[Customer ID],Facts[ProID]),

    Facts[Date] = maxdate

    )

   

 

How it works:-

the maxdate uses CALCULATE and ALLEXCEPT to get max date for the customer product

 

Then CALCULATE  and  SELECTEDVALUE and ALLEXCEPT

gets the prices for the maxdate for the customer product

 

 

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

😀

[speedramps · Accepted solution]

Thanks alialsayer for the example input date and example desired output.

I will help you on a few conditions:-

1) Please format your input data correctly.  

I am unpaid volunteer who has proper job but gives some of my spare time to teach you Power BI for free. I wont format data like d/m/yyyy to dd/mm/yyyy.  You should do that youself  

2) I problem per ticket please. You have added at least 3 problems which is an entire project, not a how do I do this/that question. 

Here is an answer to problem 1-Latest Price ($) per ProID: 

Please click thumbs up and accept it and then raise new tickets with the corrected data and quote @speedramps in the ticket text. I will receive an automated email and help you. Thank you

Click here to download a solution 

Last price =

VAR maxdate =
CALCULATE(
MAX(Facts[Date]),
ALLEXCEPT(Facts,Facts[Customer ID],Facts[ProID]))

RETURN

CALCULATE(

    SELECTEDVALUE(Facts[Price ($)]),

    ALLEXCEPT(Facts,Facts[Customer ID],Facts[ProID]),

    Facts[Date] = maxdate

    )

   

 

How it works:-

the maxdate uses CALCULATE and ALLEXCEPT to get max date for the customer product

 

Then CALCULATE  and  SELECTEDVALUE and ALLEXCEPT

gets the prices for the maxdate for the customer product

 

 

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

😀

[alialsayer]

I would really appreciate your help. It was amazing of you to provide me with this DAX function, and I understand that you are volunteering in your free time. I am speechless and cannot thank you enough. I am still having some issues when the maximum date and null price ($) are set, as I should pick the latest price available in this case. I entered data into Power BI rather than Excel, which may have solved the format problem. I will open new tickets for the other two calculations 

Power BI link Power BI 

[speedramps]

We want to help you but your description is too vaugue. Please write it again clearly.

You say "I would like to add new calculated columns " but forget to say what that column is or how you want to calaculate.   Whoops 😀

Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.
Also provide the example desired output, with a clear description of the process flow.

Remember not to share private data ... we don't want you to get into trouble. 😧

Take care to use the same table and field names in the input, output and description so we can understand your problem and help you.

You will get a quick response if you put time and effort into writing clear problem descriptions.

Vaugue descriptions can waste your time and ourtime.

Look foward to helping you when the above information is forthcoming

[alialsayer]

Thank you. I appreciate your quick response. I added more descriptions and added an excel file. I attempted to upload Excel, but there was no option, so I added my cloud and the link.

[speedramps · Accepted solution]

Hi

Please can you accept the solution and raise a new ticket.

Sorry, but if you describe the problem wrong, then that is not my fault and I expect you to accept the solution in recogntion that I fixed the problems as described.

You asked "1-Latest Price ($) per ProID:  will pick the latest price the customer selected at the same ProID at the maximum date"

For customer 87654321 prod 1 the lastest date is 08/02/2022 which has no price.

Hence no price is returned by the measure. See screen print.

Also  a raise a new separte tickets if for points 2) & 3).

Quote @speedramps in tne new tickeys and I will be happy to help.