Forum Discussion

Hayleysea's avatar
Hayleysea
Icon for Resolver II rankResolver II
6 years ago
Solved

Calculated column based on whether value is available against ID

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 IDStageDateStage 5 Date (Calculated Column)
123112/01/2020 
123212/02/2020 
123312/03/2020 
123412/04/2020xxx
123512/05/2020 
321105/01/2020 
321205/02/2020 
321305/03/2020 
321405/04/2020xxx

 

Any help is greatly appreciated!!

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    6 years ago

    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 )

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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 )
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Hayleysea
      Icon for Resolver II rankResolver II
      6 years ago

      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."