cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sdjensen Senior Member
Senior Member

Re: 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.

 

 

/sdjensen

View solution in original post

Anonymous
Not applicable

Re: Calendar table causing problems.

 

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? 

 

cap1.png

Anonymous
Not applicable

Re: Calendar table causing problems.

cap2.png


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
Not applicable

Re: Calendar table causing problems.

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 Established Member
Established Member

Re: Calendar table causing problems.

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

SortByColumn

 

Maybe this works with PowerBI as well.

Anonymous
Not applicable

Re: Calendar table causing problems.

sometimes this software is just a mystery to me.

 

cap3.PNGcap4.PNGcap5.PNG

Floriankx Established Member
Established Member

Re: Calendar table causing problems.

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.

 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)