Forum Discussion
Data type Date not sorting on a line chart
I'm trying to display some data on a line chart. Specifically, I want them to be split by week. I have a column called EventDate that contains DateTime values. To classify them by week, I created a custom column that performs the following:
Week =
if Date.IsInCurrentWeek([EventDate]) then null else Date.StartOfWeek([EventDate])
I have a page-level filter that removes anything that is null, so the conditional is there so that it doesn't display data for the current week until it's over.
When I try to display the data over a line graph, I get the following:
As can be seen from the screenshot, the date sorting is off, as it appears to be sorting it as if the labels were strings. If I click on the ... on the top right and click on Sort By Week, the problem persists. Why is this? How can I fix it? Looking at the query itself, the Week column is of type Date, not Text.
Thank you!
4 Replies
- fedparMicrosoft Employee
The problem was that, upon applying a custom query, the column loses its type. In other words, even if you're referencing from a Date column, the new custom column will not be of type Date, and you have to manually set it to be one.
- austinsenseImpactful Individual
Create a date table if you don't already have one
- fedparMicrosoft Employee
What do you mean? The data is currently being imported from a SQL database.
- MattAllingtonCommunity Champion
You can read about data table structure here http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/
and date tables here http://exceleratorbi.com.au/power-pivot-calendar-tables/