Forum Discussion
Week Number as per months
Hi Data Naughts,
I have a data set which has Date in it, I want to make a column which can show me a value like July-1st week, July-2nd week and so on. What I could do is take weeknum(Date) but i am getting July-30 week, July-31 week and so on as my output. Please help me out.
Thanks in advance
Priyanshu
4 Replies
Hi Anonymous ,
You can achieve it using below steps in Power Query:
1. From 'Date' column, create a 'Name of Month' column
2. From 'Date' column, create a 'Week of Month' column
3. Add a conditional column with below cases:
Note: Week of Month here is the column created/added in step 2
4. Merge 'Name of Month'(created in Step 1) and 'Custom'(Created in Step 3) with custom seperator hyphen/dash(-)
5. Optional Step: Remove 'Week of Month'(created in Step 2)
Final Result:
If this post solved your query, mark this post as solution and give a thumbs up !!!
Hi Anonymous ,
Please try this:
Column = FORMAT('Table'[Date],"mmmm")&" - "&WEEKNUM('Table'[Date],2)&" Week"Please try this expression in a calculated column on your Date table.
Month Week = var weekstart = 'Date'[Date] - WEEKDAY('Date'[Date])return FORMAT(weekstart, "MMMM") & " - Week " & ROUNDUP(Day(weekstart)/7,0)If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
