Forum Discussion
Convert Months to weeks
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 ! :D
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.
To Anonymous'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.
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.
Anonymous 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.
Thanks a lot, ! Thats power query right ?
Geraldine my example and the Date.EndOfWeek function are Power Query, yes.
