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

     

     

     

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

YusufHabib 

Not clear about the expected results but can you try this

Time in Years = 

IF( 
    CONTAINSSTRING(Base[Time],"Q") , RIGHT(Base[Time],1)/4,
    IF(
        CONTAINSSTRING(Base[Time],"Y") , RIGHT(Base[Time],1)/1
    )
)

 

 



  • 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
    )
)