Forum Discussion
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
Ok - silly question - where steps do I take, within PBI, to create the Date Table? (ie where do I plop that formula down)? (embarrassed)
And an even more embarrassing question - when you say edit "via DAX or PQ" - how do I know exactly which is which here...I KNOW that sounds silly. So, for example, I wanted to go into "Transform Data" to then "add a column" and do the concantenate there originally, but it wasn't recognizing "Concantenate" there. I had to, from my main PBI screen on the data tab - right click the table and add column that way - THEN I could use it in a formula there.
I ask because I had just gone into the Power Query Editor to transform the data to make that column (contancentanted) a date/time form - but the column wasn't there....
Resolver INo 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
So, something is not working (see second screen shot).
For my dataset, the first screen shot is what I'm pulling in. All are columns from a SQL statement to the database. I create the date via the DAX formula I'm pointing too, then I converted that to a date/time...which changed the format of the column.....but that's my "date" that I'm trying to use for my date table.
I KNOW i've said this a million times, and I know you've been very patient - I just have to say again, thank you for your direction and help!
Just to add - there *IS* a date in my table (screen shot below). I started trying to follow the video you linked and when I jus type
"Date = CALENDARAUTO()" it SHOULD return dates - but it's saying it cannot find a Date/Time in the model - yet...as screen shot below shows - I do have a date.
Pulling what little hair I have out...lol- tom480
Hi Anonymous ,
I think the issue is Date/Time. Make the column type a Date type.
Then use this to create your Date Table:
TableDT =VAR MinYear = YEAR ( MIN ( 'Invoice Data'[Invoice Date] ) )VAR MaxYear = YEAR ( MAX ( 'Invoice Data'[Invoice Date] ) )RETURNADDCOLUMNS (FILTER (CALENDARAUTO( ),AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )),"Calendar Year", "CY " & YEAR ( [Date] ),"Month Name", FORMAT ( [Date], "mmmm" ),"Month Number", MONTH ( [Date] ))We will figure it out! Tom
tom480 - when this is all said and done - you NEED to somehow let me Paypal you something - even small - to thank you for your time.
(NOTE: I did upload a copy of the table as an Excel file to my Google Drive here. I only removed the customer table. Not sure if this helps)
So, I believe I had tried that. Here's where I'm changing it back to DateAnd still getting error:
- tom480
No worries! And you don't owe me anything, glad to help. I took the google sheet and downloaded a copy and imported into PBI, , changed the format of the invoice date column to the desired, then created my date table and marked as date table. I have included a copy of my pbix for review. I think this will help! Tom 😀
Date tableInvoice Date Column FormatMatrix with sorting
Getting back to this and ready to scream. I was able to open up your report (after updating my PBI Desktop version) and it pulls in ok. However, I'm still unable to modify my version. I need to stick with this as it has the SQL connection to pull in new invoices. I'm not sure why I'm getting "CALENDARAUTO function can not find a base column of DateTime type in the model"...other than I'm not truly pulling in a "date" from the database, I'm pulling in a Month and Year and then converting it to a date in the Combined_Date column as a Date type (see first screen shot). Then, I create a new column ("New Date") which is just equal to the Combined_Date column but as a "Date/Time" type (second screen shot). I don't know if I need to create a new forum question just on this alone? It's making me feel entirely stupid. I'd send you the file, but not sure how to remove the SQL connection that has, obviously, the connection details - while leaving the data in tact.