Forum Discussion

YusufHabib's avatar
YusufHabib
New Member
5 years ago
Solved

Converting quarters & years into numerical values

I am trying to write a simple DAX query to convert a time series (written as text) into numerical equivalent in order to use in power BI charts. The time series values are in the form of "PD_Q1", "P...
  • VahidDM's avatar
    VahidDM
    5 years ago

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

     

     

     

YusufHabib's avatar
YusufHabib
New Member
5 years ago

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?

Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
5 years ago

YusufHabib 

I posted my solution as well, this will accommodate any size of the text before Q or Y:

Time in Years = 
VAR __LEN = LEN(Base[Time])
VAR __LOC_Q = SEARCH("Q",Base[Time],,0) 
VAR __LOC_y = SEARCH("Y",Base[Time],,0) 

RETURN

IF( __LOC_Q > 0 , RIGHT(Base[Time],__LEN - __LOC_Q)/4,
    IF( __LOC_y > 0, RIGHT(Base[Time],__LEN - __LOC_y)/1
    )
)