Converting quarters & years into numerical values

[YusufHabib]

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

[Fowmy]

@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]

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]

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

[VahidDM]

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]

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]

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

[YusufHabib]

@VahidDM 

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 · Accepted solution]

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]

Thanks! I didn't realize that columns and measures have differences in formula.

It works now.