Forum Discussion
Calendar table causing problems.
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.
Microsoft EmployeeHI 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.
- Phil_Seamark
Does your sales data have a date column that shows the day of the transaction?
- No only week and year are specified.