Forum Discussion

alialsayer's avatar
alialsayer
Icon for Helper I rankHelper I
3 years ago
Solved

Calculating Dax upgrade or downgrade

  If I have four given columns Date Customer ID ProID Price ($) Each ProID represents a sequence of consecutive days and the price ($) is a subscription might vary during ProID as per custome...
General Comment
Need Help
Tutorial Requests
  • speedramps's avatar
    speedramps
    3 years ago

    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's avatar
    speedramps
    3 years ago

    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.

     

     

     

speedramps's avatar
speedramps
Icon for Super User rankSuper User
3 years ago

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's avatar
    alialsayer
    Icon for Helper I rankHelper I
    3 years ago

    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