Forum Discussion
Converting quarters & years into numerical values
Oh, Do you want to add a calculated column?
You can use my codes as a Measure, not a new Column.
If you want a column, use this code:
Time in Years = Var _Len = LEN(Base[Time] )-4 RETURN IF( CONTAINSSTRING(Base[Time],"Q") , RIGHT(Base[Time],_Len)/4, IF( CONTAINSSTRING(Base[Time],"Y") , RIGHT(Base[Time],_Len)/1 ) )If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Thanks for the quick answer but the query doesn't work for PD_Q10 or PD_Y10. Is there a way to make it work in those cases?
Super UserHi YusufHabib
Try this:
Time in Years =
Var _Len = LEN(MAX ( Base[Time] ))-4
VAR _2R =
RIGHT ( MAX ( Base[Time] ), _Len+1 )
VAR _Y_Q =
LEFT ( _2R, 1 )
VAR _N =
RIGHT ( _2R, _Len)
RETURN
IF ( _Y_Q = "Q", _N / 4, _N )
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Really appreciate your prompt replies.
I pasted the code and recieved error 'Expressions that yield variant data-type cannot be used to define calculated columns'.
- VahidDM
Oh, Do you want to add a calculated column?
You can use my codes as a Measure, not a new Column.
If you want a column, use this code:
Time in Years = Var _Len = LEN(Base[Time] )-4 RETURN IF( CONTAINSSTRING(Base[Time],"Q") , RIGHT(Base[Time],_Len)/4, IF( CONTAINSSTRING(Base[Time],"Y") , RIGHT(Base[Time],_Len)/1 ) )If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Thanks! I didn't realize that columns and measures have differences in formula.
It works now.