Calculated Column Help

[Renjith]

Friends

Could you please help me to update the below formula to acheive case 4 of below requirement.

Using this formula I have acheived till 3rd requirement

Formula

Baseline Variation =
var a = if(ISBLANK(Sheet1[Result]),0,Sheet1[Result])

var b = maxx(filter(Sheet1,Sheet1[ID]=EARLIER(Sheet1[ID]) && Sheet1[Flag]="Y"
&& Sheet1[Test]]=EARLIER(Sheet1[Test]])
&& Sheet1[Cat]=EARLIER(Sheet1[Cat]) ),Sheet1[Result])

var c =IF(ISBLANK(b),0,b-a)
return if(Sheet1[STATUS]="NOT DONE",BLANK(), c)

Requirement

Case 1 : ID having multiple rows and 1 row having Y value in Flag Field

ID    Test         Cat            Flag       STATUS            Result          Baseline Variation
1        A            C1                                                       5                       10
1        A             C1                                                    10                        5
1        A             C1             Y                                     15                        0
1        A              C1                                                    25                      10
1        A             C1                                                    35                        20

Case 2 : ID having single row and No Y value present in Flag Field, Set baseline as 0

ID                  Test              Cat                 Flag              STATUS        Result       Baseline Variation
2                      B                 C2                                                             5                               0

Case 3 : IDs have STATUS field value = NOT DONE, set baseline as blank

ID                  Test             Cat                  Flag            STATUS       Result        Baseline Variation
3                     C                 C3                                    NOT DONE

Case 4 : IDs have Result field value = blank, set baseline as blank

ID              Test             Cat           Flag              STATUS     Result                     Baseline Variation
4                   D              C4

[mwegener]

Hi @Renjith 

is your problem solved?
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[mwegener]

Hi @Renjith ,

try this

Baseline Variation =
VAR a =
    IF ( ISBLANK ( Sheet1[Result] ), 0, Sheet1[Result] )
VAR b =
    MAXX (
        FILTER (
            Sheet1,
            Sheet1[ID] = EARLIER ( Sheet1[ID] )
                && Sheet1[Flag] = "Y"
                && Sheet1[Test] = EARLIER ( Sheet1[Test] )
                && Sheet1[Cat] = EARLIER ( Sheet1[Cat] )
        ),
        Sheet1[Result]
    )
VAR c =
    IF ( ISBLANK ( b ), 0, b - a )
RETURN
    IF ( Sheet1[STATUS] = "NOT DONE" || ISBLANK ( Sheet1[Result] ), BLANK (), c )

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.