cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Olivecrona Regular Visitor
Regular Visitor

Calendar query by Week of Year

Hi!

 

I would like to create a Calendar by doing a blank query and inserting some M-code!

I know how to do a regular calendar and how to get the week of the year and so on.

 

What I want to do is a calendar where the lowest granularity of information is the week, not the day as if you do a normal Calendar!

 

So instead of having a Date Column consisting of:

DATE
2018-01-01
2018-01-02

I will have 

WEEK

1

2

3

 

How can I write M-code for that purpuse without modeling an original Calendar?

 

/Sebastian

5 REPLIES 5
v-xjiin-msft Super Contributor
Super Contributor

Re: Calendar query by Week of Year

Hi @Olivecrona,

 

To get the week number in Power Query, you can try Date.WeekOfYear() function. 

 

1.PNG

 

Thanks,
Xi Jin.

Olivecrona Regular Visitor
Regular Visitor

Re: Calendar query by Week of Year

Hi and thanks for your answer!

Your solution is unfortunately not a solution for my problem since the key is to get a unique week.

Your solution has the premise of dates and therefore gives every week by date wich results in the same week number 4-5 times.

What I'm looking for is instead a column with unique values of week, like you have the unique values of dates in your left column.

Example:

"Column Week" 

2

3

4

 

/Sebastian

v-xjiin-msft Super Contributor
Super Contributor

Re: Calendar query by Week of Year

Hi @Olivecrona,

 

Sorry for delay.

 

I'm not quite understand your requirement. And yes, I'm using a calendar table with every date as premise to calculate the week number. So it will return duplicate week numbers. But, if you want the unique week numbers, you just need to put the single week number column into a table visual. Power BI will remove the duplicate values automatically.

 

1.PNG

 

In your scenario, did you mean that you did not want to use dates as premise? If so, how could we know they're week numbers instead of just integer numbers?

 

Thanks,
Xi Jin.

Olivecrona Regular Visitor
Regular Visitor

Re: Calendar query by Week of Year

Thank you for your reply @v-xjiin-msft !

You are correct that I do not want date with granularity "days" as my premise, but the weeknumber of the year.

My problem is not that of a visual nature but of relational nature.

In my scenario, my sales data comes with granularity "weeknumber of the year" and I want a calendar table with the same "granularity", hence a calendar with days is not appropriate.

 

I think I can make a Calendar with granularity "days" and then group them by WeekofYear, but then my calculations is not managable

 

//Sebastian

 

sdjensen Senior Member
Senior Member

Re: Calendar query by Week of Year

@Olivecrona - I think that the right thing to do here is not to create another calendar table to match your sales budget by adding a column to your sales budget data that calculates a date for each row - in your case I would calculate the first date of each week based on your year and weeknumber and then make a relationship to your normal calendar dimension using the new date column.

/sdjensen