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.
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?
Solution SageHi 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.
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.
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?
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.
FloriankxIn PowerPivot (Excel) you have the possibility of sorting by column.
Maybe this works with PowerBI as well.
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.