comparing values with previous values and print the largest value

[vishnuprashanth]

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

[v-xjiin-msft · Accepted solution]

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

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.

[vishnuprashanth]

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 🙂 

[vishnuprashanth]

@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