Forum Discussion
Sorting and Comparing Columns Question
Good Day!
I had two questions, please. The first, I know has to have been answered. I get close in searching but the answers don't appear to work or don't answer the question specifically.
1) I have a SQL table that has a month and year column. I concantenate them in Power BI to have a year/month column. I am trying to sort so that it shows "2021/1" then "2021/2" rather than sorting as a string and putting "2021/10". I've tried creating an index column where value is 2*year + month and then sorting by that. I sorts properly in the data view, but then when I go the the visual (as pictured) it doesn't update.
2) How would I got about comparing the current month's invoice to the previous month and then either:
- highlighting the month if it's more than, say, 10% plus or minus 10 precent from the previous month or
- perhaps there's a column in between the dates that is checked if it is off by 10 percent?
In other words - some how signifying if the current month deviates from the previous month by 10% up or down?
If this can only be done by the last months - and not historically - that is an option - but would be nice to show a history....
Any help would be much appreciated!
THANK YOU!
Rob
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
20 Replies
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. 😉
awesome Anonymous !
don't forget to mark the replies as solutions so others have it easy to find it 🙂
just give a ping if you need more help!/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Hi Anonymous ,
To get the columns to sort correctly, make sure your column is marked as a date type. Next you can format the field to show the desired yyyy-mm.
I populated a few rows of data just to show the column sorting functionality, not a great dataset but suffices.
Next you want to create a basic date table so you can use the time intelligence features in Power BI and DAX. Here's some sample code to use:
TableDT =VAR MinYear = YEAR ( MIN ( Table1[Period] ) )VAR MaxYear = YEAR ( MAX ( Table1[Period] ) )RETURNADDCOLUMNS (FILTER (CALENDARAUTO( ),AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )),"Calendar Year", "CY " & YEAR ( [Date] ),"Month Name", FORMAT ( [Date], "mmmm" ),"Month Number", MONTH ( [Date] ))From there you simply create the relationship between the date table and the orginal table, with the key being the period column and now you can use the quick measure for time reporting or create your own more advanced time intelligence measures.If this works for you, please mark as a solution to help others find solutions too! Let me know if I can help with anything else. Enjoy! TomWhen marked as a date column, your sorting will work correctly (not concatenated text).In power query, column type Date (could be in DAX too though)Change to desired format...Create a quick data table to use time intelligence in Power BI / DAXUse the quick measures to get the most common measuresForgot to include step: Mark as date table...
WOW - tom480
First, thank you. Thank you very much. This is a lot to consume at once and, I hate to embarrass myself, I am very new to PBI and so some of this is difficult to grasp right now. The main way of pulling in data at this point has been just pulling in data from MS SQL tables so all that is going on in your TableDT creation is first time seeing and just trying to figure out where even to start with what I have - which is just two tables, one that lists my customers (companies) and then one that just pulls in the invoice amount and the month and year.
So my first thing I'll say (question) is - when you mention about making sure that a particular field is a date, the invoice table, believe it or not, does not have a date column...just a month and a year column. So when I created my "combined_date" column I just concantenated those into 2022/1, for example. Thus not sure that I could even convert that to a date field - but I'm probably skipping by a few steps here. Just trying to figure out where to start. Here's some screen shots on the data I have.
I think if I can grasp what you're teaching me here, it will go a long way to me furthering my understanding of PBI. I cannot thank you enough for your time.
Hi Anonymous ,
For the highlighting, I'd suggest to create a measure that calculates the difference. Then, you can use that measure to do the color coding. Let's start with the initial situation from my previous example:
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.
The first measure (that you already have) is in my case this one:
ValueMeasure = SUM ( TableA[Value] )
Second, we need another measure that calculates the difference (%) between each month. I just created this one quickly, but pressumably you need to calculate it differently. The point of my reply is not showing how to calculate such a differences but more how you can use such a measure for color coding:
ValuesMeasureDiff% = VAR _valueCurrentMonth = CALCULATE ( [ValueMeasure], TableB[DateIndex] = MAX ( TableB[DateIndex] ) ) VAR _valuePreviousMonth = CALCULATE ( [ValueMeasure], ALLEXCEPT(TableA, TableA[Type] ), TableB[DateIndex] = MAX ( TableB[DateIndex] ) - 1 ) RETURN DIVIDE (_valueCurrentMonth - _valuePreviousMonth, _valuePreviousMonth ) + 0
Here the result using both of the measure underneath:
Now you can select the table visual and in the Visualisations tab on the right hand side, click on the downwards arrow on the Values field. Choose conditional formatting and then background color:
Fill in the settings accordingly. Make sure to use your Diff% measure:
And here the result:
There is even the possibility to create a color coding measure in DAX, which is pretty cool, too! It's less of a hassle if you wanna reuse the same settings on mutliple visuals.
Here you can find more information:
Conditional table formatting in Power BI Desktop - Power BI | Microsoft Docs
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/tom480 , I pray I haven't worn out my questions yet. I was finally able to get the dates to work. The problem was is that it wasn't recognizing the "DAX" created date, I had to go into PQ and create a date that way. My problem now is in understanding some of the formulas you put down. Also, at the bottom, just to be sure, I have a screen shot of the relationship I built. I got a little confused because you referenced (in one of the messages above, it gets confusing following the trail) something about a "period" I just created a reference between the two dates. Is that ok?
So I believe I only have this I'm confused on:
1) The first measure, ValueMeasure, (I haven not created Measures yet). Does it matter which table I "right-click" and create measure with?
2) I assume that in ValueMeasure = SUM ( TableA[Value] ), that "TableA" is my Invoice Data table, but not sure what [Value A] is?
3) Can you help walk me through the logic of in _valueCurrentMonth and Previous Month? and do I create them in the same manner as above in #1?
VAR _valueCurrentMonth = CALCULATE ( [ValueMeasure], TableB[DateIndex] = MAX ( TableB[DateIndex] ) ) VAR _valuePreviousMonth = CALCULATE ( [ValueMeasure], ALLEXCEPT(TableA, TableA[Type] ), TableB[DateIndex] = MAX ( TableB[DateIndex] ) - 1 )
My apologies if these are stupid questions.