Forum Discussion
Month Order / Multiple Tables
- 2 years ago
Just to confirm, you have a FACT table that has Year, Month, SKU and QTY; you have a DIM table for SKU that has SKU, Color, Description, etc., and you have a date table, correct? If this is accurate, you will need to have a bridge table to connect your dim date table to your fact table. The bridge table would have year and month and a concatenation to be the key. You would need the same concatenation on your DIM Date and your fact table so that you could join the fact to the bridge table.
For your ordering on the months, instead of using a calculated column, go into Power Query and add a conditional column (if Month = Jan then 1, add rule and if Month = Feb then 2, etc.). Then, in Power BI select Month and sort by the month number column you added in Power Query. Or, if you switch to the Date Reference table from SQLBI that I talk about in the blog, it already has month number as a field that can be used to sort Month Name.
Have a column with month number set to number and then go to your month name field and go to the Column Tools ribbon that appears, click on Sort by column and select month number. This will sort your month name by month number. Sort one column by another column in Power BI - Power BI | Microsoft Learn