Convert Months to weeks

[Geraldine]

Hello Guys., Hope you can suggest something

I have a column with "X" numbers of rows with amounts and linked to another column with dates, but just by Month , (I can add year-Q-month and day ) but I need to split the information by WEEK, and I just have this format for date -  01/01/2016 -02/01/2016 - 03/01/2016 - 04/01/2016 etc etc , how would you split those (let s say 500 rows) with date 01/01/2016 by weeks ? any ideas ? I need for those rows equally distributed a week column with this :

1/31/2016

1/24/2016

1/17/2016

1/10/2016

1/3/2016

Your help will eb highly appreciated !    😄

[Geraldine · Accepted solution]

Hello everyone, I found a very simple solution ! I just split the total I had in colum (YTD) btw 52 weeks , and that result multiplied per the week we are currently ( week 25). Thank you all for your help !  😄

[kcantor]

@Geraldine

 Re-do

I have actually dealt with this before. I simply split the column in PowerQuery to only show the end date (last part of the range). I used a date table that I keep as a date dim with week numbers assigned. I related the dates and then put the week number on my axis.

[KHorseman]

For this sort of thing it's usually best to have your fact table with a date column linked to a separate date table. That date table would have a continuous series of dates covering the full range you need, plus other columns with category info related to those dates (week, month, year, quarter). My preferred method for week is to use the last date of the week from the date in question. In your query you can write a custom column using the Date.EndOfWeek function.

Here's one version of the query I use to create date tables. There are lots of other examples around as well.

[Geraldine]

Thanks a lot, ! Thats power query right ?

[KHorseman]

@Geraldine my example and the Date.EndOfWeek function are Power Query, yes.

[Greg_Deckler]

To @KHorseman's point, if you had a Date table like DateStream in the Azure Data Marketplace, you could relate your fact table to your date table on the actual date in the fact table (if it is there) and your date table should be able to tell you what week of the year that date falls on.

[KHorseman]

Yeah, that's pretty much what I'm trying to describe. You would never expose your actual date column in any visual on the report; you'd only put the Week column from the date table down, and the relationship between your table's date column and the date table's primary date column would do all the work for you.

I keep meaning to check out that DateStream table. My date table is an ok example, but it sure does load slowly. Too many calculated columns in the query I'm sure.

[Greg_Deckler]

@KHorseman here is the direct link to it:

http://datamarket.azure.com/dataset/boyanpenev/datestream

Free and you only need to download it once per data model.

[Greg_Deckler]

I'm not clear on your data format (original) but I am betting that WEEKNUM will play a role in this:

https://msdn.microsoft.com/en-us/library/ee634572.aspx

[Geraldine]

Thank you, !  That could be a solution if I use DAX , but in this case I am using power query ... 😞