Forum Discussion
Calculated column based on a value or a previous value
- 1 year ago
Here is another way to do it. This grabs all the rows of same ID, with same or earlier year, where Start = B; then checks if the result is not empty (ie we returned at least one row that meets the criteria). We then just cast the boolean to INT to get the desired output. We use VAR to capture our values from earlier row context rather than EARLIER*.
FLAG = VAR _thisYear = Data[Year] RETURN CALCULATE( CONVERT( NOT ISEMPTY( Data ), INTEGER ), ALLEXCEPT( Data, Data[ID] ), Data[Start] = "B", Data[Year] <= _thisYear )*EARLIER was a needed function before variables were introduced in DAX. Now, "it is recommended using variable (VAR) saving the value when it is still accessible, before a new row context hides the required row context to access the desired value." ( https://dax.guide/earlier )
Here is another way to do it. This grabs all the rows of same ID, with same or earlier year, where Start = B; then checks if the result is not empty (ie we returned at least one row that meets the criteria). We then just cast the boolean to INT to get the desired output. We use VAR to capture our values from earlier row context rather than EARLIER*.
FLAG =
VAR _thisYear = Data[Year]
RETURN
CALCULATE(
CONVERT( NOT ISEMPTY( Data ), INTEGER ),
ALLEXCEPT( Data, Data[ID] ),
Data[Start] = "B",
Data[Year] <= _thisYear
)
*EARLIER was a needed function before variables were introduced in DAX. Now, "it is recommended using variable (VAR) saving the value when it is still accessible, before a new row context hides the required row context to access the desired value." ( https://dax.guide/earlier )
Worked perfectly, I appreciate your help.