How to get Year and Quarter recognized as date?

[Anonymous]

Hi, 

I have quarter and year in my data set. I want PowerBI to recognize and deal with these as a date (Time Intelligence, Drill Down etc., Hierarchies etc). 

How do I do this? 

Thanks!

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please refer to my pbix filw to see if it helps you.

Create a column.

date_ =
VAR _year =
    LEFT ( MAX ( 'Table'[YearQua] ), 4 ) & "/"
VAR _right =
    RIGHT ( 'Table'[YearQua], 1 )
VAR _date =
    IF (
        _right = "1",
        "1/1",
        IF (
            _right = "2",
            "4/1",
            IF ( _right = "3", "7/1", IF ( _right = "4", "10/1", BLANK () ) )
        )
    )
RETURN
    _year & _date

Then change the column type.

Then click OK.

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

I see that you are not following my example.

First of all, get the datatypes right.  These 'Any' datatypes are not really useful for further data manipulations.  You can fix this by selecting all columns and choosing Detect Data Type from the Transform menu.

Then Add a custom column to make a month number. This will be the 'if' statement (similar to my first post above).

Then add another custom column using #date to construct the date from the 3 columns you have.  You can experiment by hardcoding the values to see how it works e.g. : #date(2020,3,1).  If you make this work, substitute the column names for each number (in the correct place)

[HotChilli]

Combine the values (with 1 for the day) in a custom column in Power Query.

You can make a date in the following way : 

#date(year as number, month as number, day as number)

You will probably have to write an 'if' statement to get a month from the quarter e.g if quarter = "q2" then 4

[Anonymous]

Thanks for your help. I still have not quite managed to get that to work.

How would you do it for following example?

Thank you!