Forum Discussion
Calendar table causing problems.
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
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.
16 Replies
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 :)
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.
Does your sales data have a date column that shows the day of the transaction?
