Forum Discussion
two months in one week ??
Hi
I have a dataset which is based on weeks data. I would like to show it on monthly and or quartely level.
I am running into issues when trying to link it to the date dimension table, because some weeks fall into two months.
Exeample: the week 35 is in two months in August as well as in September.
What I would like to acheive is that week 35 will be month September. That way I will get unique values in my week date dimension table and will be able to link it to the sales data i have.
Hope you guys can help me with this.
Thanx
Column = month(date(year(now())-1;12;31)+w[WeekNumber]*7)
5 Replies
- az38
Community Champion
Where are you define year? it should be calculated for current year?
- az38
Column = month(date(year(now())-1;12;31)+w[WeekNumber]*7)
ThimResolver V
You can use this Formula in DAX to get the first date of the week besides all dates of the same week.
Startdate of week = [Date] + ((7- WEEKDAY([Date];2))-6)
Then use this to extract the month.
Hope this will help.