Need some help with Date (bridge) table for drop down menu

[JustinDoh1]

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".

    Date Bridge = DISTINCT(union(all('Table_1'[New Processing Date]),all(Table_2[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?

[JustinDoh1]

@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?

[v-yiruan-msft]

@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

[DataNinja777 · Accepted 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,