Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to Solution.
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 )
@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())
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."
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |