Calculated column based on whether value is available against ID

[Hayleysea]

Hi There,

I have a table with project ID's, stages and dates. Stages range from 1 to 5 but not all project ID's have a stage 5 entry. In the case they don't have a Stage 5 entry I need to use Stage 4 date plus 1 month to get the Stage 5 date. 

For example: project ID 123 has a Stage 5 date available so I will use 12/05/2020 but 321 doesn't have a stage 5 entry so I will use the stage 4 date of 05/04/2020 + 1 month = 05/05/2020.

All projects have a Stage 4 date so ideally I would like the Stage 5 date calculation to be in the same line as the Stage 4 but in a new calculated column (where xxx is below)

Project ID	Stage	Date	Stage 5 Date (Calculated Column)
123	1	12/01/2020
123	2	12/02/2020
123	3	12/03/2020
123	4	12/04/2020	xxx
123	5	12/05/2020
321	1	05/01/2020
321	2	05/02/2020
321	3	05/03/2020
321	4	05/04/2020	xxx

Any help is greatly appreciated!!

[Anonymous]

@Hayleysea please create a calculated column as per below

Column = 
VAR stage4date = CALCULATE(MAX(Test[Date]),ALLEXCEPT(Test,Test[Project ID]))
VAR _maxstage = CALCULATE(MAX(Test[Stage]),ALLEXCEPT(Test,Test[Project ID]))
RETURN SWITCH(TRUE()
                ,AND(Test[Stage]=4,_maxstage = 5),stage4date
                ,AND(Test[Stage]=4,_maxstage=4),DATEADD(Test[Date].[Date],1,MONTH)
                ,BLANK())

 

[Hayleysea]

Thanks for the response. This is the error I get when implementing this solution. I have found the solution in another reply.

"Function 'SWITCH' does not support comparing values of type True/False with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values."

[Zubair_Muhammad · Accepted solution]

@Hayleysea 

Try this calculated column

Column =
VAR Stage5_present =
    NOT (
        ISEMPTY (
            CALCULATETABLE (
                VALUES ( TableName[Stage] ),
                ALLEXCEPT ( TableName, TableName[Project ID] ),
                TableName[Stage] = 5
            )
        )
    )
VAR Value_I_Want =
    IF (
        Stage5_present,
        CALCULATE (
            MAX ( TableName[Date] ),
            ALLEXCEPT ( TableName, TableName[Project ID] ),
            TableName[Stage] = 5
        ),
        EDATE (
            CALCULATE (
                MAX ( TableName[Date] ),
                ALLEXCEPT ( TableName, TableName[Project ID] ),
                TableName[Stage] = 4
            ),
            1
        )
    )
RETURN
    IF ( [Stage] = 4, Value_I_Want )