Month Order / Multiple Tables

[Rustin788]

Hey,

I'm setting up a new report.  For this report, I get the daily values from a simple dataset that just has (Year, Month, SKU, Cases Sold) that updates daily.  I have another data set that has information on the SKU's (description,color, images, etc).  There are more SKUs in the data set than I need so the SKU table filters those down for me. 

I can't figure out how to get my data sets to properly go from January to December.  I have a fiscal calendar table setup that I use for other things, but for this report, I need to be connected to the SKU table.  

DataSet1: Year, Month (As "Jan"), SKU, QTY   (Shows 2023 & 2024 data) (Year and Month are set as TEXT)

DataSet2: SKU, Color, Description, Image...

What I've tried:

- Adding in an index column and sorting by index

- Used a dax function to change the months (written as "JAN" to just "1") Tried "1" as both text and a number

I'm working with line chart, matrix, and tables.

If needed I can try and have someone adjust the raw data report I get, but since I don't have direct access to that I would like to try and fix on my end first.

Thanks for any help!

[Rustin788]

@audreygerred 

I think because I used the below DAX to create the month number field it is giving me a circular dependency error.

FullMonth =

    SWITCH(TRUE(),

        CONTAINSSTRING(Roll_Towel[Month], "Jan"), "1",

        CONTAINSSTRING(Roll_Towel[Month], "Feb"), "2",

        CONTAINSSTRING(Roll_Towel[Month], "Mar"), "3",

        CONTAINSSTRING(Roll_Towel[Month], "Apr"), "4",

        CONTAINSSTRING(Roll_Towel[Month], "May"), "5",

        CONTAINSSTRING(Roll_Towel[Month], "Jun"), "6",

        CONTAINSSTRING(Roll_Towel[Month], "Jul"), "7",

        CONTAINSSTRING(Roll_Towel[Month], "Aug"), "8",

        CONTAINSSTRING(Roll_Towel[Month], "Sep"), "9",

        CONTAINSSTRING(Roll_Towel[Month], "Oct"), "10",

        CONTAINSSTRING(Roll_Towel[Month], "Nov"), "11",

        CONTAINSSTRING(Roll_Towel[Month], "Dec"), "12",

        BLANK()

    )

[audreygerred]

Ideally you will have a date table and the month number is just part of the table. Here is a blog post I have about the date reference table I use: http://powerbiwithme.com/2023/08/01/the-custom-date-table-edition/

[Rustin788]

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?

[audreygerred · Accepted solution]

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.

[Rustin788]

So sorry, I got pulled into something else and had to work on that for a bit.  Yes, your table descriptions sound accurate.  I will see what I can do with creating a bridge table.

[audreygerred]

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