Calendar query by Week of Year

[Anonymous]

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

[v-xjiin-msft]

Hi @Anonymous,

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

Thanks,
Xi Jin.

[Anonymous]

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]

Hi @Anonymous,

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.

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.

[Anonymous]

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]

@Anonymous - 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.