Forum Discussion
Month Order / Multiple Tables
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.
I have a date table I use. When I try to mark it as a date table it tells me the date column can't have dates. My date table has Year, Fiscal Period, Fiscal Month, Fiscal Quarter, and Date and goes from 1/1/22 - 12/30/24 (The last day of our 2024 fiscal year).
If I am able to set that as the date table, would I be able to use the dates there and still connect to my other SKU reference table?
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.