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", "PD_Q2" upto "PD_Qxx" along with values in the form of "PD_Y1", "PD_Y2" upto PD_Yxx".
 
I would prefer to convert them into simple year equivalents so that the series has 0.25 for "PD_Q1", 0.5 for "PD_Q2" and so on... I wrote a DAX query based on some searching as below where the new measure is "Time in Years" and the time series is Base[Time]. I would like to simply search the data for quarters and divide the numerical part by 4 or search for years and simply extract the numerical part. The query doesn't even run as it says that IF function has incorrect syntax.
 
Many thanks for your help!
 
Time in Years =
VAR d_year = 0

IF(
SEARCH("Q", Base[Time], 1, 0) <> 0,
d_year = RIGHT(Base[Time], len(Base[Time])-SEARCH("Q", Base[Time])) / 4
)

IF(
SEARCH("Y", Base[Time], 1, 0) <> 0,
d_year = RIGHT(Base[Time], len(Base[Time])-SEARCH("Y", Base[Time]))
)

RETURN d_year
  • 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!!

     

     

     

9 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    5 years ago

    Hi YusufHabib 

     

    Try this measure:

    Time in Years = 
VAR _2R =
    RIGHT ( MAX ( Base[Time] ), 2 )
VAR _Y_Q =
    LEFT ( _2R, 1 )
VAR _N =
    RIGHT ( _2R, 1 )
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!!

     

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

      • VahidDM's avatar
        VahidDM
        Icon for Super User rankSuper User
        5 years ago

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

         

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