cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

comparing values with previous values and print the largest value

Hello,

I have a dataset, in which I have added a new column to find the maximum value for each person for each week. 

Now, I need to add another column, that compares the value for each person with the previous week's value and takes in the larger value from it. 

Column F in the attached image has the description of how it is calculated. I do not any idea of whether this could be even possible to do using DAX function. 

 



Compare Person A's Week 1 value with Week 2 value, If week 2 is higher, print week 2 value, else week 1. Similarly, compare week 3 for person A with week 2, if week 3 is higher, print week 3, else week 2. 

Is this possible in Power BI? Any help or suggestions, please?

Thanks and Regards,

Vishnsdsd.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
v-xjiin-msft Super Contributor
Super Contributor

Re: comparing values with previous values and print the largest value

@vishnuprashanth 

 

To achieve your requirement, you can create a calculated column with DAX expression like:

 

Max Value Target =
MAXX (
    FILTER (
        Person,
        Person[Person] = EARLIER ( Person[Person] )
            && Person[Weeks] <= EARLIER ( Person[Weeks] )
    ),
    Person[Value]
)

1.PNG

Or measure:

Max Value Target Measure =
CALCULATE (
    MAX ( Person[Value] ),
    FILTER ( ALL ( Person[Weeks] ), Person[Weeks] <= MAX ( Person[Weeks] ) ),
    ALLEXCEPT ( Person, Person[Person] )
)

 

Thanks,
Xi Jin.

3 REPLIES 3

Re: comparing values with previous values and print the largest value

@Zubair_Muhammad Is there any way to do this? comparing the previous week values and take the larger value? 

is it possible to do it in Power BI? Sorry, there was no response to this question since yesterday, so I thought of tagging you to see if you could help on this!! 

 

Thanks & Regards,
Vishnu

v-xjiin-msft Super Contributor
Super Contributor

Re: comparing values with previous values and print the largest value

@vishnuprashanth 

 

To achieve your requirement, you can create a calculated column with DAX expression like:

 

Max Value Target =
MAXX (
    FILTER (
        Person,
        Person[Person] = EARLIER ( Person[Person] )
            && Person[Weeks] <= EARLIER ( Person[Weeks] )
    ),
    Person[Value]
)

1.PNG

Or measure:

Max Value Target Measure =
CALCULATE (
    MAX ( Person[Value] ),
    FILTER ( ALL ( Person[Weeks] ), Person[Weeks] <= MAX ( Person[Weeks] ) ),
    ALLEXCEPT ( Person, Person[Person] )
)

 

Thanks,
Xi Jin.

Re: comparing values with previous values and print the largest value

Perfect. Exactly what I was looking for. And thanks for giving me both the solutions, I just realized the difference between calculating as column and as a measure. Thanks a ton @v-xjiin-msft.

Really helpful Smiley Happy