Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Qmars007
Frequent Visitor

Date format issue

Hi, 
I hope some experts help me with a date format issue. 
The format of my date column on the data source, an excel file, is Q1 20/21, Q2 20/21, Q3 20/21, Q4 20/21, Q1 21/22..... So when I import the flat file into Power BI, the quarterly KPIs are not synchronised as the date format is in text format.
I would like to know the best solution to overcome this date format so that my quarterly KPIs for each quarter be shown correctly.
Best,
Qmars

2 REPLIES 2
v-yanjiang-msft
Community Support
Community Support

Hi @Qmars007 ,

You cann't change the quarter to date format, as in all the date formats, there isn't one format for showing quarters.

vkalyjmsft_0-1647415152106.png

For your calculate purpose, you can create a calculated column in the table contains date, like this:

Qtr =
"Q" & QUARTER ( [Date] ) & " "
    & FORMAT ( [Date], "YY" ) & "/"
    & FORMAT ( [Date], "YY" ) + 1

Then the quarter relates to date, and can also relates to the quarterly KPIs.

vkalyjmsft_1-1647415694064.png

Best Regards,
Community Support Team _ kalyj

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

 

amitchandak
Super User
Super User

@Qmars007 ,

Based on Month start date , create a date , Assume FY start from Jan

Date = Eomonth(date(mid([FY Qtr],4,2), 4,1)  , (mid([FY Qtr],1,1)-1)*3 )

 

Then you can use time intelligence

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors