cancel
Showing results for
Did you mean:
Member

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,

Vishn

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

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

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

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
Member

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

Super Contributor

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

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

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.

Member

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.