Forum Discussion
Date range issue
Hi,
I have imported an Excel file and now have a column (image below) with years set out as:
1991-92
1992-93
1993-94
and so on to 2029-30. They are just a financial year, I have no need to display quarters, months etc.
When I have used them in a line chart with years on the x-axis the years have been muddled up (image below). Perhaps Power BI can't recognise them as a sequence of years.
They are currently set as text, if I transform the column to date I receive an error for most years and the remainder change to 1/01/2000 format (image below).
X-axis showing muddled years:
Year column:
Transformed year column:
Any pointers to a solution or suggestions would be appreciated.
Cheers,
Brent
Anonymous , The format YYYY-YY is self sortable. from three dots it should sort .
Else you can create a fate column like
In power Query
#date(Number.FromText(Text.Start([Year],4)),1,1)
or dax
Date(Left([Year],4),1,1)
In case you create a column in DAX
also create
Year 1 = [Year]
and use Year 1 in visual and this should have a date as the sort column
why refer : How to Create Sort Column and Solve Related Errors: https://youtu.be/KK1zu4MBb-c
2 Replies
Anonymous , The format YYYY-YY is self sortable. from three dots it should sort .
Else you can create a fate column like
In power Query
#date(Number.FromText(Text.Start([Year],4)),1,1)
or dax
Date(Left([Year],4),1,1)
In case you create a column in DAX
also create
Year 1 = [Year]
and use Year 1 in visual and this should have a date as the sort column
why refer : How to Create Sort Column and Solve Related Errors: https://youtu.be/KK1zu4MBb-c