Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am trying to make my Date table work by possibly adding a column (or possibly a logic) so that I could Sort properly.
I have uploaded my PowerBI file here.
As you can see on the bottom screenshot, the lastest "New Processing Date" is May 2024.
I have pulled data from SQL, and it is a varchar data type so that I could show "Current = ".
The issue is that since this is Varchar data type, not a Date type, sorting is alphabetical.
I would like to display "New Processing Date" as Date asc.
How do I go about fixing this Date table?
Currently, I used Dax to combine two fields from two tables together to create a Date table called "Date Bridge", and it is used for the drop down menu of visual for "New Processing Date".
Is there a way to add a new column in "Date Bridge" table and sort by "Processing Date" since this column is a date column?
Does Date table only allow one column?
I am trying to utilize having a separate a Date table "Date Bridge" for the design of this visual instead of using "New Processing Date" column from Table_1 or Table_2.
But, if there is no other way to sort properly, I guess I would have to use "New Processing Date" from either Table_1 or Table_2, but I don't think that is a good design, right?
Solved! Go to Solution.
Hi @JustinDoh1 ,
There are multiple ways to achive your required sort order and one of them is to use the calculated calendar table and summarize it for the month year field, and then sort "mmmm yyyy" field which was alphabetically sorted originally by "yyyy-mm" to ensure that the sort order respects numerical month order instead of alphabetical sort order.
A calculated calendar table with month granularity can be produced by the following dax formula.
Calendar month =
SUMMARIZE (
ADDCOLUMNS (
CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2024, 12, 31 ) ),
"yyyy-mm", FORMAT ( [Date], "yyyy-mm" ),
"mmm-yyyy", FORMAT ( [Date], "mmm-yyyy" ),
"mmmm yyyy", FORMAT ( [Date], "mmmm yyyy" )
),
[mmm-yyyy],
[mmmm yyyy],
[yyyy-mm]
)
Then, you can create a calculated column in the calculated calendar table to identify the Current month from your fact table.
You can fix the sorting issue of "mmmm yyyy" by using the following user interface in the table view.
The resultant output shows the properly sorted mmmm yyyy field which identifies Current month properly.
This is one of many ways to achive your required output. I attach an example pbix file.
Best regards,
@DataNinja777 Thank you so much for your help. I think this would work. Let me take a deeper look later.
As far as relationship with two other tables (Table_1 & Table_2), can I make them Active? Any consequences?
@DataNinja777 Thanks for your contribution on this thread.
Hi @JustinDoh1 ,
You can set the relationship between Calendar month and Table_1 and Calendar month and Table_2 as active. The important thing to note here is that there can only be one active relationship between two tables. When discussing any potential consequences, could you clarify which side you are referring to? Is it related to data display or another aspect?
Create and manage relationships in Power BI Desktop
Best Regards
Hi @JustinDoh1 ,
There are multiple ways to achive your required sort order and one of them is to use the calculated calendar table and summarize it for the month year field, and then sort "mmmm yyyy" field which was alphabetically sorted originally by "yyyy-mm" to ensure that the sort order respects numerical month order instead of alphabetical sort order.
A calculated calendar table with month granularity can be produced by the following dax formula.
Calendar month =
SUMMARIZE (
ADDCOLUMNS (
CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2024, 12, 31 ) ),
"yyyy-mm", FORMAT ( [Date], "yyyy-mm" ),
"mmm-yyyy", FORMAT ( [Date], "mmm-yyyy" ),
"mmmm yyyy", FORMAT ( [Date], "mmmm yyyy" )
),
[mmm-yyyy],
[mmmm yyyy],
[yyyy-mm]
)
Then, you can create a calculated column in the calculated calendar table to identify the Current month from your fact table.
You can fix the sorting issue of "mmmm yyyy" by using the following user interface in the table view.
The resultant output shows the properly sorted mmmm yyyy field which identifies Current month properly.
This is one of many ways to achive your required output. I attach an example pbix file.
Best regards,
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
17 | |
17 | |
16 |
User | Count |
---|---|
28 | |
27 | |
18 | |
14 | |
14 |