Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[may Leese]

I am new to powerbi and have created a simple chart with date filed (weekly) on the x axis, but this is not showing in date order, how can I change this?

——————————
may Leese
Analyst
07716020880
——————————

[William Rodriguez]

Hi :

The remedy is to ‘sort the column’. The following two videos should get you going in the right direction. 

Microsoft Video: https://www.youtube.com/watch?v=dk6FJFVkdHs

Pragmatic Works Video: https://www.youtube.com/watch?v=fUnOfrSjxCU

(I think the Pragmatic one will be more helpful)
William

——————————
William Rodriguez
Business Analyst | MCSA: BI Reporting
——————————
——————————————-

[David Gerard]

Hi May Leese,

Use the “Sort by column” feature in the ribbon. Basically, you sort one column by another. In one column you list your weeks, and in a second column you list the order in which you want your list to show up.

Hope that helps. Let us know

——————————
David Gerard
Buisness Intelligence Consultant
Kampala
779152992
——————————
——————————————-

[Christopher Majka]

Is your date field of Date data type or is it number or text?

——————————
christopher majka
Manager, Business Intelligence
4018682000
——————————
——————————————-

[Tim Dyeson]

?Hi May,
A couple of questions: Is your date column formatted as a date or is it a text field? If it’s text, then it will not sort properly on it’s own. Are you using a date table in your model? We use a custom date table in all of our data models to be able to control the X axis in many ways.

I typically use a text value in my X axis to display months (e.g., Jan-2019) and have to use the “Sort by Column” feature in the Modeling ribbon.

If you are not able to get the date to sort properly, you may want to create a date table with a WeekOfYear colum to sort it (screen shot below).

I’ve attached an Excel date table and a PBIX that loads it. You will just have to change the Source path.

——————————
Tim Dyeson
Performance Measurement Manager
WA State Health Care Authority
——————————
——————————————-

[Nancy Peterson]

If you click on the visual you have created, there are the three dots in the corner for more options. One of the options is to set the sort order.

——————————
Nancy Peterson
Senior Business Analyst
Pitsco Education
Pittsburg KS
——————————
——————————————-

[Ethan Owens]

Hi May, 

     Normally when I create a date dimension, I have a fully qualified date field and other descriptive fields.  Below is a DateDimension I created using DAX:

DIM_DateDax =

VAR BaseCalendar = CALENDARAUTO()

RETURN

GENERATE (

BaseCalendar,

VAR BaseDate = [Date]

VAR YearDate = YEAR ( BaseDate )

VAR MonthNumber = MONTH ( BaseDate )

VAR MonthName = FORMAT ( BaseDate, “mmmm” )

VAR YearMonthName = FORMAT ( BaseDate, “mmm yy” )

var Quarter = CONCATENATE(“Q”,roundup(Month(BaseDate)/3,0))

var WeekNumber = WEEKNUM(BaseDate,2)

RETURN

ROW (

“Year”, YearDate

,”MonthNumber”, MonthNumber

,”MonthName”, MonthName

,”YearMonth”, YearMonthName

,”QuarterName” , Quarter

,”WeekNumber”, WeekNumber

)

)

Once the dimension is loaded, I go to the modeling tab, Sort by Column, and sort MonthName by MonthNumber.  This allows you to use text fields rather than dates should you choose to do so.

——————————
Ethan Owens
45459
Centerville
9373291211
——————————
——————————————-