Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |