Dates Order inconsistent


#1

This is driving me mad!
I use the Data function provided. However if I want a table for (eg) Months only it displays in Alpha form, so Apr, Aug, Dec, Feb…
when obviously I want it chronologically.
Should be simple. What is going wrong? Where is it taking the order from? The Date Table is sorted on Date dd/mm/yy. I assumed it would be that.


#2

In your date table you need to sort the month by the month number column.

In your PBIX file select the Date table in the Data Tab on the left -> Go to Modeling tab -> Select Month column -> then sort by icon and select month number column

Guy


#3

Hi Guy
I see your thinking but not working I’m afraid.
I have a date table sorted on dd/mm/yyyy and then other rows, Month, Year & Month, FY, yymm, mmyy, etc all derived from it. The Date table is sorted on dd/mm/yyyy. I would have thought that if I created a table and just choose Month (filtered for a specific year) the Months would show in the sequence taken from the Date table but they don’t.
I can get round it somewhat by using a numeric display for month - so April = 04, May = 05 etc. But then the visualisation shows numbers not literal display.

I can use the hierarchy in the Date selection (Year, Qtr, Month, Date) to get the same result, but I would have thought that the tables would take their sort sequence from the Master sort in the table.

So do people display reports by Literal Month or some other derivative or always use the Date hierarchy


#4

Peter,

Strange, would have thought that would work. Just a question - The Date table you are using is from the code that Sam gives out or from someplace else??

The reason I ask is that I’ve been using Sam’s code and have no issue sorting my monthnames by the month number.

Can you provide a copy of the Date table to look at??

Guy


#5

Create a new date column called “month Number” and sort your month column by the month number


#6

Hi Guy

So here is the screen shot:

Hope that makes sense.
I have a date table loaded from Sam’s code. All the date columns are shown on the RHS. You can see on the screen shot I have a table with Date(Date), and that sequences correctly.
I then have another table with the column MonthInCalendar showing CostsMTD. The sequence of the months is alphabetic, but the total figures are correct.
I then have another table keyed on MMM-YY. This is to show budgets which are held in a Budget table and a value for each of Supplier / Customer for the month, MMM-YY. This MMM-YY column is held as a link table, connecting to the Date table via a derived MMM-YY column, and the relevant budget table. This maintains a 1:Many relationship either side of the MMM-YY table. In the data model I have given this MMM-YY table and index, 1-12, and have that as the Sort column. So in the Data Tables, it sequences correctly.

But when I put it on the dashboard it comes out all wonky!


#7

Peter,

Try this and see if it helps.

Your attachment show that you are getting the Dates from the hierarchy and not the Dates Table in you visuals.

I’d suggest that you sort the MonthName by the MonthofYear column and use the Date table in the visuals.

This seems to work for me in my Reports

Guy


#8

Hi Guy, thanks for your help. Here is what I am trying to do.

I have a Customer Budget set monthly. This table has Value, Customer and Month, but Month is held as MMM-YY, ie Text.

I have a standard date table and set of near daily transactions. I want to show Sales vs Budget so spin through the sales table, get a number, spin through the Budget table get a number. To allow the Date table to link to the budget table I created the Link Table to connect MMM-YY in the budget to dates in the Date table. I also created a MMM-YY table in the dates table.

So what I have with the various combinations as follows:

The first row are from the Date Table. Various values of Total Sales which are correct because Sales table and Date table share dd/mm/yy. For Budget all wrong because Date Table can’t find a corresponding dd/mm/yy value in the budget table.
But the interesting thing, calculation aside, is the sequence of dates. In table 1.3 the chronological sequence is correct. Table 1.4, the column MMM-YY taken from the Date table, yet the sequence is wring. It is arranged alphabetically.

On the second row, I’ve taken the date format from the link table. Now the numbers are correct for sales and budget. However in Table 2.2 the sequence is wrong. In table 2.1 the sequence is correct but the date format is wrong. For this I created a Date column of the middle of each Budget month just to see.
In the Link Table I created an Index column, 1-12, and made this the sort column. I would have thought the visuals would take this as the sort sequence but no. It seems to default to alphanumeric, unless it is a date formatted column in which case it use’s that. But I can’t get a date format in MMM-YYY that BI recognises.

I’ve got something wrong because it shouldn’t be this complicated, but I’ve no idea what!


#9

Peter,

Two questions:

Are the budget and sales numbers correct even if out of order?? Means the measures are correct.
Can you upload a copy of the pbix, if not sensitive??

What I’m thinking is that something is off in the relationships and I’d like to get a look at it over the weekend.

Please no sensitive data - if possible.

Guy


#10

Here we go Guy.

Slimmed it down and cleaned it down. You can see the answers are the same.

One other thing that doesn’t make sense is the table called CustClass. This is a classficiation table of all customers. You can see it has an index and the index is sorted, 1-5. Yet on the visual it comes out alpha numeric.

If you get a chance let me know

Supplier Lens. CLEANpbix.pbix (1.5 MB)


#11

Let me pull this down and take a look at it today.

Guy


#12

To arrange the MMM-YY values in chronological order, you will need a number column in the Dates table in the format YYYYMM (e.g. 201601, 201701, 201801), then sort the MMM-YY column by that one.

For the CustClass issue, the visuals will only sort by the values assigned to them. However, the same principal applies here as for the dates. If you want the customer names to sort by the index value, select the customer name and sort by the index. You are basically overriding the default sort behavior of the column.

The table linking to Dates when you don’t have a unique value on one side (e.g. one to many relationship) will fail. So MMM-YY with many rows in a fact table will not link to MMM-YY in a Dates dimension (also many rows). Already beat my head against that one :expressionless:.

Sam has a number of topics covering the lookups needed in DAX to get the Budgets data for a given MMM-YY. If someone else knows the exact topics, please post them (all of my data so far is granular enough to not need that, but I’ve come across the topics in my training).

Hope this helps!


#13

Peter,

I think I have what you want. Here is what I did:

I copied the 2 Measures you used to ctreate the MMM-YY & YY-MMM columns then deleted those columns from the Date Table
I sorted the MonthInCalendar column by the MonthOfYear column in the Date Table.
I created the MMM-YY column again by using the same Measure you had used
I sorted the MMM-YY column by the MonthOfYear column
I created the YY-MMM column again by using the same Measure you had used

Tables 1.1 - 1.5 now sort correctly

Table 2.2 needed sorted (in the visual) by the index column

I’m uploading the revised PBIX

Hope this helps

Guy

Supplier Lens. CLEANpbix - Copy.pbix (1.5 MB)


#14

Hi John

Thanks for this a little progress.
The Cust Class did sort when I sorted the Class by the Index. It showed in the Table view as correct sequence but on the Visual it was incorrect until I did the Sort. This is what I am finding confusing, the sort sequence in the Table is not replicated in the Visual.

For the MMM-YY I don’t follow your logic. The Budget table has a customer entry per month, so lots of Month, MMM-YY values. I need to create a 121 relationship with date, so I created the link table which only has one MMM-YY value on it. Table 2.1 shows what happens if I sum sales by the context provided by this table, it works correctly.

If I use this link table to sum all sales in the sales table, BI goes from this table, finds all long dates related to a value of MMM-YY in the link table, and adds all the sales for that MMM-YY. That is the answer in 2.2 and 2.1.

I wouldn’t have expected it to work in (eg) Table 1.3 as the date MMM-YY in the date table has no unique relationship with the Budget figures. What I did do though was delete the relationship from the link table to the budget table, and connected the date table to the budget table through the long date, as there is only one long date in the budget table, the mid point of the budget month. That creates a correct Budget and Sales figure in all cases.

The core problem still remains which is getting MMM-YY to display in a chronological list not an alphanumeric list.

I think the problem is in SORT. I tried sorting the Link Table but it wont sort as all the values are derived from each other. So I created the same as the Link table in Excel and imported it. Now that displays and sorts correctly, and all values are correct. That gets round the problem

However I still don’t see why a row in the date table, say Month, would display alphabetically in a visual when it’s Sort sequence is chronological, unless it is something to do with the fact that it is a Text field derived from the initial value, as it is in the M code that creates the table.


#15

@Boney, you are correct in your last paragraph about the data type being Text. The sort order to your human mind might be chronological, but the computer has no idea that “Apr 16” comes after “Feb 16” unless you give it other information.

When you add columns to a visual, the visual decides how to default sort the data. I often have to tell the visual to sort by the total dollars descending instead of the categorical value ascending, for example. In a table visual, the default sort will depend on the columns added, the sequence of adding them, etc. until you indicate the column you want to sort by and the ascending/descending selection (which is accomplished by selecting the column header in the visual). You then still have to deal with the sort order in the underlying table, as you are seeing with the MMM-YY value.

In the actual table (accessed from the Data left sidebar menu item), you can select each column and then select Modeling > Sort by Column from the ribbon menu. I see that the “Monthnyear” column is already there in the required numeric format. Select the MMM-YY column, select the Sort by Column ribbon menu item, and then select “Monthnyear” in the dropdown as shown in the screen shot. Table 1.4 will be fixed immediately. If you also then use MMM-YY in Table 1.5, it will be fixed as well.


#16

Peter,

Not to disagree with the above posts but the entire sort has to start from the first column used - MonthInCalendar must be sorted by MonthOfYear

The rest will sort correctly because you base the Calculated columns in the Date Table on that original column.

Anything else has no reference to go back to.

Guy