Help to assign month based on week number

[MitchTrott]

Hi all,

I am new to powerBi and have ran into a problem that i cannot seem to find the answer to.

My scenario is;

I have 3 columns: Job ID, Date (YYYYWW), Hours Booked

The data in the Date column looks like this "202111"

I have thousands of rows with this data so i need to consolidate it more efficiently.  I'd like to add columns that include the months but this will cause issues as some weeks will be split across multiple months so i would need to calculate some sort of ratio of the week that crosses each month and apply that ratio to the number of hours booked. 

As you can see, this is a fairly niche and complicated question so i am findng it difficult to find the answers and ghuidance i need online. 

I have created a date table but my lack of knowledge is blocking me from doing anything further with it.

Any and all help is greatly appreciated.

[flath]

Hi, @MitchTrott 

I annalyzed your request and couldn't figure out any way to consolidate your data more efficiently that wouldn't rely on transforming your data with some good time calculation. But, in order to actually help you on this, it would be better if you could explain a little bit more about how you would like to present and use your data.

Working with 'hour' and 'week' data can be very tricky, so we better know what the business need before starting the job. One way or another, you'll need to split your column "Date" into two columns: "Year" and "Week" but it may not be enough.

Regards

[v-chenwuz-msft · Accepted solution]

Hi @MitchTrott ,

Can we do it the other way around? First, create a calendar table and get the yyyymm of the date in this calendar table. Then divide the hours by count days during that week(directly 7,but the first week of this year is not 7) to get the average hours of that week for each day. 

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.