Sorting and Comparing Columns Question

Hi Anonymous ,

Here a walkthrough on the first part of your question.

We have two tables, TableA is our FactTable and looks like this:

TableB is our Date dimension including a Date and the respective Index

The tables are connected via the Date column.

When using a matrix, without utilizing the DateIndex, we get the following result:

Obviously, PBI orders the strings alphabetically. As you suggested, we need an index column which PBI can use to sort it accordingly.

This is how you do it:

Go to the Data tab, select the column you'd like to sort (Date), click on Sort by column (Column tools ribbon) and choose the index column (DateIndex)

And the result should be like this:

Note, the Index Column needs to be in number format!

Let me know if his helps! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

SO, I was able to figure out the sorting combining the index value I created (tweaking it to 3*year + month) - but here is what I didn't know, that I got from your answer - that you could sort a column based upon another column. When I did that, I got it to work. NOW, I just need to understand the min/max and % difference solution you had. 😉

No worries, everyone is here to help.  You can create a date table in Power Query or in DAX in the table itself.  I will show you that later in this screenshot.

1) go to the table icon in the left navigation;

2) click on new table;

3) paste in your simple date table code.  There's lot of examples to use, this is one from SQLBI.com.  Remember in my code example, I am pointing to Table1[Period] which would represent the concatenated column you created - yours will likely be named something else;

4) Mark as date table;

5) Select the column that is the date column (will be Date);

6) Click OK.  When successful you will see the 'Validated sucessfully' notification.

Always glad to help - Tom 

Create a date table