Convert week numbers into months

[darmstrong]

Hi. How do I convert week values into months? Note, the original data is not a date, simply a value specifying the fiscal week. I would like to know how to convert these weeks into months e.g. week 1, 2, 3, 4 all = Jan, etc.

There are several posts similar to this issue but they are not quite the same. I have a column of week numbers and years e.g. 01.2019, 02.2019 etc. these are not formatted as dates. I have split these out into separate columns of week no. and year, but now want to reference this data to a separate table that only has data summarised. in columns. by month e.g. April 2019. 

I believe the simplest solution is to add a column to the first set of data which indicates what month a week falls into, and then (hopefully) compare these figures to the separate table of monthly figures.  

Apologies if I have made some basic errors in my approach to this, I am new to this, so please feel free to recommend alternative approaches. 

Thanks

[MattAllington · Accepted solution]

• You should build a dimensional model. https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/
• Get a calendar table that does contain dates. https://exceleratorbi.com.au/power-pivot-calendar-tables/
• you can keep your surrogate date key (the one you mention) in the data table as long as it also exists in the calendar table as long as it also exists in the cake dar table (for the join) you need to use “mark as date table“ from the modelling menu if you join on a surrogate key.
• You can learn how to build a calendar table here 

https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/