Check multiple comparison

[alexumagba]

Hi All

I have the following situation to resolve in powerBi Desktop

There is a sales record for 4 different weeks, week 1 to week 4.

I want to create a measure that will check customers whose purchase has drop for four consecutive weeks

This is what I have in mind

if week 1<week 2<week 3<week 4, "Consecutive Drop", "Normal"

How can I go about this.

Customer Name	Week 1	Week 2	Week 3	Week 4	Drop
0001	23	34	12	32	Normal
0002	23	21	18	12	Consecutive Drop
0003	48	32	21	19	Cosecutive Drop
0004	98	72	12	3	Consecutive Drop
0005	19	43	78	101	Normal

[VahidDM · Accepted solution]

Hi @alexumagba 

Try this measure:

Drop Measure = 
VAR _W1 =
    MAX ( 'Table'[Week 1] )
VAR _W2 =
    MAX ( 'Table'[Week 2] )
VAR _W3 =
    MAX ( 'Table'[Week 3] )
VAR _W4 =
    MAX ( 'Table'[Week 4] )
RETURN
    IF ( _W1 > _W2 && _W2 > _W3 && _W3 > _W4, "Consecutive Drop", "Normal" )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube