Calculated column with conditions

[Vick143]

Hi,

I am trying to create a new calculated column called Inforce by contract group using the following data sample, I have added that column to showcase expected result. These are the conditions for calculated column: If Inforce Flag colunn value is "Inforce" for a specific Contract Group during specific Priod then repeat "Inforce" for all rows of that Contract Group and Period combination Else "-". 

I hope it is clear, Thank you in advance.

ContractNbr	Contract Group	Period	Inforce Flag	Inforce by contract group (Expected Result)
TP0295875A-2014-1	TP0295875A	202012	-	Inforce
TP0295875A-2015-1	TP0295875A	202012	-	Inforce
TP0295875A-2016-2	TP0295875A	202012	-	Inforce
TP0295875A-2017-2	TP0295875A	202012	-	Inforce
TP0295875A-2018-2	TP0295875A	202012	-	Inforce
TP0295875A-2019-2	TP0295875A	202012	-	Inforce
TP0295875A-2020-2	TP0295875A	202012	Inforce	Inforce
TP0295875A-2014-1	TP0295875A	202103	-	-
TP0295875A-2015-1	TP0295875A	202103	-	-
TP0295875A-2016-2	TP0295875A	202103	-	-
TP0295875A-2017-2	TP0295875A	202103	-	-
TP0295875A-2018-2	TP0295875A	202103	-	-
TP0295875A-2019-2	TP0295875A	202103	-	-
TP0295875A-2020-2	TP0295875A	202103	-	-
DS4586324D-2014-1	DS4586324D	202203	-	Inforce
DS4586324D-2015-1	DS4586324D	202203	-	Inforce
DS4586324D-2016-2	DS4586324D	202203	-	Inforce
DS4586324D-2017-2	DS4586324D	202203	-	Inforce
DS4586324D-2018-2	DS4586324D	202203	-	Inforce
DS4586324D-2019-2	DS4586324D	202203	-	Inforce
DS4586324D-2020-2	DS4586324D	202203	-	Inforce
DS4586324D-2021-2	DS4586324D	202203	Inforce	Inforce

[v-zhangti · Accepted solution]

Hi, @Vick143 

You can try the following methods.

Result = 
CALCULATE ( MAX ( 'Table'[Inforce Flag] ),
    FILTER ('Table',
        [Contract Group] = EARLIER ( 'Table'[Contract Group] )
            && [Period] = EARLIER ( 'Table'[Period] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Samarth_18]

Hi @Vick143 ,

You could create a column as below:-

expected_Column = 
VAR result =
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Contract Group] = EARLIER ( 'Table'[Contract Group] )
                && 'Table'[Inforce Flag] = "Inforce" && 'Table'[Period] = EARLIER('Table'[Period])
        )
    )
RETURN
   IF ( result > 0, "Inforce", "-" )

Below is the file for reference

[wdx223_Daniel]

CalculatedColumn=MAXX(FILTER(Table,Table[Contract Group]=EARLIER(Table[Contract Group])&&Table[ Inforce Flag]="Inforce"),Table[ Inforce Flag])