date in text to date format

[Anonymous]

Hi  I have date in test format "Q1-2020". can I do  like this if it is"Q1-2020" date range = 01/01/2020 to 03/01/2020. If anyways I can change date Q1-2020 text in date format as coulumn in data. Please help

[v-rzhou-msft]

Hi @Anonymous 

I build a table like yours to have a test.

You may build a calendar table to achieve your goal.

Calendar =
VAR _MinYear =
    MINX ( 'Table', 'Table'[Year] )
VAR _MaxYear =
    MAXX ( 'Table', 'Table'[Year] )
RETURN
    ADDCOLUMNS (
        CALENDAR ( DATE ( _MinYear, 1, 1 ), DATE ( _MaxYear, 12, 31 ) ),
        "QTR", QUARTER ( [Date] ),
        "Year", YEAR ( [Date] ),
        "Column1",
            "Q" & ""
                & COMBINEVALUES ( "-", QUARTER ( [Date] ), YEAR ( [Date] ) )
    )

Result:

You can download the pbix file from this link: date in text to date format

Best Regards,

Rico Zhou

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

[amitchandak]

@Anonymous , refer my blog or video for this , if those can help

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

https://www.youtube.com/watch?v=Qt0TM-4H09U&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=5

[mahoneypat]

Do you want a different text value in same row that shows "1/1/2020-3/31/2020"?  Or you want to generate a table of dates over that range?

Regards,

Pat

[Anonymous]

Hi

thanx for understanding. Yes I want to generate column/table over the date range in date format 

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

Since the Q1 has 3 month in it , you can't change the type of "Q1-2020" from text to Date format directly.

And you can build a calendar table as I replied before and build relationships between two table's column1.

Table:

Calendar Table:

Relationship:

Build a Slicer to see the Date range for "Q1-2020"

Result:

You can download the pbix file from this link: date in text to date format

Best Regards,

Rico Zhou

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

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[pranit828]

Hi @Anonymous 

measure1 = 
VAR year = RIGHT(Table[date in test format],4)
RETURN
SWITCH(LEFT(Table[date in test format],2),"Q1","01/01/"&[year]&" to 03/01/"&[year],
                                           "Q2","04/01/"&[year]&" to 06/01/"&[year],
                                           "Q3","07/01/"&[year]&" to 09/01/"&[year],
                                           "Q4","10/01/"&[year]&" to 12/01/"&[year])