Half year from quarter - Q2 for first and O4 for second half

[olijo]

I need the new column for half year from quarterly data: for first half Q2 and for second half Q4, something like:

half year	quarter	value	new column
2004.1	2004.1	1	2
2004.1	2004.2	2	2
2004.2	2004.3	3	4
2004.2	2004.4	4	4
2005.1	2005.1	5	6
2005.1	2005.2	6	6
2005.2	2005.3	7	8
2005.2	2005.4	8	8
2006.1	2006.1	9	10
2006.1	2006.2	10	10
2006.2	2006.3	11	12
2006.2	2006.4	12	12

Can you help me with formula for calculated column - new column in this table?

[olijo]

Better version of table, so I need formula for last column:

half year           quarter          value           new column
2004.1              2004.1             1                      2
2004.1              2004.2             2                      2
2004.2              2004.3              3                     4
2004.2              2004.4              4                     4
2005.1              2005.1              5                     6
2005.1              2005.2              6                     6
2005.2             2005.3               7                     8
2005.2             2005.4               8                     8
2006.1             2006.1               9                     10
2006.1             2006.2              10                    10
2006.2             2006.3             11                    12
2006.2             2006.4             12                    12

[VahidDM · Accepted solution]

Hi @olijo 

I'm not sure I correctly understand your question or not.

I think you want to have a column to show the Q2 value of each year for all Q1 and Q2 items of that year, and Q4 for the Q3 and Q4 items of that year.

If I'm correct, first you need to add one column to merge year, half, and quarter numbers to one value (like parent-child hierarchy):

parent-child = 
VAR _Year =
    LEFT ( 'Table'[half year ], 4 )
RETURN
    VAR _Half =
        RIGHT ( 'Table'[half year ], 1 )
    RETURN
        VAR _Quarter =
            RIGHT ( 'Table'[ quarter ], 1 )
        RETURN
            _Year & "." & _Half & "." & _Quarter

The output will be like this:

Then you can use the Parent-Child column to add a new Column with the required values:

New Column=
VAR _Year =
    LEFT ( 'Table'[half year ], 4 )
RETURN
    VAR _Half =
        RIGHT ( 'Table'[half year ], 1 )
    RETURN
        VAR _Quarter =
            RIGHT ( 'Table'[ quarter ], 1 )
        RETURN
            VAR _EvenQ =
                _Year & "." & _Half & "."
                    & EVEN ( _Quarter )
            RETURN
                LOOKUPVALUE ( 'Table'[value ], 'Table'[parent-child], _EvenQ )

Output:

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!