Date Sorting in Line Chart

[patquevillon]

Hello,

I want to display data ranging from November 2016 to now (March 2017, but will keep growing) in a line chart. My date field is a date (properly recognized as a date Hierarchy) and I want to keep the hiearchy mode and provide my users the ability to drill up-down through days, months, quarters, years.

Issues are the following: Power BI does not sort or aggregate properly.

- At the Day level the data is aggregated on the day numerical value (1 thru 30/31) so my axis has 0-31 data points instead of the expected 120 or so entries for the last 4 months.

- At the month level the data is sorted by month, but irregardless of the year. So I get : Jan, Feb, Mar, Nov, Dec Instead of the expected Nov, Dec, Jan, Feb, Mar (Nov and Dec being in 2016 while Jan, Feb, Mar are in 2017).

- At the Quarter level: same as for months. Q1 comes before Q4. I am expecting Q4 (2016) to come before Q1 (2017)

Any solutions to this ?

examples:

thanks

[parry2k · Accepted solution]

First question comes in my mind, do you have dedicated continuous calendar table? if not follow this link as example to create one and then use the fields from calendar table on your axis to achieve what you are looking for. Let me know if you need further help.

Thanks,

P

[patquevillon]

Thank you Parry2k!

You put me on the right track. I ended up creating a Calendar table using your instructions (not as many fields and tweaking the syntax as my French Operationg System doesn't like the ";" ).

I was then able to link my source table to the Calendar on the date field and simply use the Calendar day, year-month and year-quarter as the elements of my axis hierarchy. Works like a charm.

[parry2k]

Good to hear. Always get good feeling when there is a solution.

Cheers