Calendar table causing problems.

[Anonymous]

Hi all,

I have tried different calendar tables out there, but none seem to help me with matching the data i have.

I basically have a table with sales data per week and year, but Power BI treat my week number as "whole numbers", and if i change the data type to "date" then it turns into some 1900s years.

If i keep the week number as numbers and try to match it with week number from my calendar table then it fails.

here is my calendar table formula:

Date =
ADDCOLUMNS (
CALENDAR (DATE(2015;1;1); DATE(2021;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)

Please help.

best,

Jakob

[Phil_Seamark]

HI @Anonymous

I would create a column in your 'Matas_Weekly_Sales' table that is the actual date (use a date time) for the start, or end of each week period the transaction belongs to. 

Then create a relationship between this new column and the [Date] column in your 'Date' table.  Do not try to create a relationship to the [Week_number] column in your table.  It won't work and you don't need to 🙂

[Anonymous]

Hi @Phil_Seamark

Thank you for the reply. 

So you are saying that i should create a new column in the sales data file that somehow transform the year and weeknumber into a specific date within the week of that week number and year.

How exactly can i do this? I am completely new to Power BI.

[Phil_Seamark]

Does your sales data have a date column that shows the day of the transaction?

[Anonymous]

No only week and year are specified.

[KeithChu]

So your data is already aggregated by year by week? If so, then add a calculated column in both table that concatenate [Year] and [Week] as a key and join them.

[Anonymous]

Hi @KeithChu

thanks for reply.

So your data is already aggregated by year by week: YES

Unfortunately i still get error message.

Please see attached pictures of what i did.

Now i have made 2x concatenate columns in each of the data sets and i try to link them together and then i get error message.

[sdjensen]

In your table with sales data you could calculate the first date of the week with using the formula below (replace the column references "Year" and "Week" with the names of the columns in your table), and then connect this column to your calendar table.

FirstDayOfWeek = DATE([Year];1;-2) - WEEKDAY(DATE([Year];1;3)) + [Week]*7

[Anonymous]

Hi @sdjensen

😞

I dont understand what you mean. 

Remember i am compelely new to Power BI so i need the full process described to get there. This is what i did:

Click "edit queries" click "transform" ribbon then select the column named "Year" in the Sales Data table and then i dont understand your instructions anymore. 

I should replace the column with a name in my table? what name and where in the table? and where do i use your formula?

[sdjensen · Accepted solution]

Hi @Anonymous,

My code is not M code (power query language) it is a DAX formula to calculate a new column in your sales table.

1. Go to the "Data" tab (icon looks like a table in the menu in the left side) (Don't enter "Edit Queries")

2. Go to your table with your sales transactions

3. On the "Home" tab in the top menu select "New Column" under "Calculations" (just to the left of the Publish button)

4. Insert this code in the formula field "FirstDayOfWeek = DATE([Year];1;-2) - WEEKDAY(DATE([Year];1;3)) + [Week_number]*7" (and press enter)

You now have a new column in your sales table the should hold the first date of the week calculated from your year and week column and you should be able to make a relationship between this column and your date column in your calendar table.

[Anonymous]

Thanks a lot! It worked @sdjensen

However, one small problem.. the dates as you can see on the picture are not jan, feb, march... how can i sort them? 

[Anonymous]

I 


I also tried to use the date table to show the sales and its the same "sorting" problem.. it shows both the quarters and years randomly.

[Anonymous]

Actually the first one works because i acsendingly sorted in the table by date, but the drill down still dont seem to work. 

I would like to have it by year first and then drill down into quarter and week number, but it appears to be randomly sorted.

[Floriankx]

In PowerPivot (Excel) you have the possibility of sorting by column.

SortByColumn

Maybe this works with PowerBI as well.

[Anonymous]

sometimes this software is just a mystery to me.

[Floriankx]

Hello,

the little arrow doesn't affect your Pivot Table or diagram.

It is just for displaying inside the editor. Even filter the data won't have any affection.

[Floriankx]

Your date table containes unique Dates but each week has seven days. So each week number appears seven times a year multiplied by the number of years. So week number is no unique value to create a relationship with.

This is why @sdjensen suggested you to add a Date to your table so you can relate this to the date column of your date table which contains unique values.