Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hayleysea
Resolver II
Resolver II

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!!

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@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 )

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

@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())

test1.png 

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
Community Champion
Community Champion

@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 )

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.