cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sabeensp Member
Member

Week of the Month (Calculated Column)

Hello,

 

I have a Date Dimention table, in which I need to add calculated column (Week_Of) shown below in the image. I tried several IF Then else, but not beign able to do it so far. Can you please help?

2019-07-17_8-43-45.jpg

2 ACCEPTED SOLUTIONS

Accepted Solutions
KHorseman Super Contributor
Super Contributor

Re: Week of the Month (Calculated Column)

Oops my mistake, I misread.

 

WeekStarting = DimDate[Date] - WEEKDAY(DimDate[Date]) + 1

Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
KHorseman Super Contributor
Super Contributor

Re: Week of the Month (Calculated Column)

That's an easy enough change. WEEKDAY starts on Sunday by default, but you can change it to start on either Saturday or Monday by changing the second argument.

 

WeekStarting = DimDate[Date] - WEEKDAY(DimDate[Date], 2) + 1

Check out the documentation of WEEKDAY here.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
6 REPLIES 6
KHorseman Super Contributor
Super Contributor

Re: Week of the Month (Calculated Column)

If you were creating this in the query editor it would be easy. There is a Date.WeekofMonth formula. So I'm going to assume you're writing this in DAX. The easiest way I can think of is to use the WEEKNUM formula, which returns the week number in the year. Subtract the WEEKNUM of the first week of the month from the weeknum of the week you're on, and that gives you the week of the month. Don't use the date of the first of the month though, because you'll mess up on weeks that start at the end of the previous month. Use the date of the last day of the week to find the first weeknum of the month.

 

WeekOfMonth = VAR weekending = DimDate[Date] - WEEKDAY(DimDate[Date]) + 7
RETURN WEEKNUM(weekending) - WEEKNUM(DATE(YEAR(weekending), MONTH(weekending), 1)) + 1

Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
sabeensp Member
Member

Re: Week of the Month (Calculated Column)

@KHorsemanThanks, Yes, I'm doing that in DAX.

 

Your solution gives me the number of week, which I already have in column Day_Wk_Num.
What I'm looking for is what I have in column "Week_Of", which is basically the date of each week begining date, repeating for each day of that week.

 

2019-07-17_8-43-45.jpg

KHorseman Super Contributor
Super Contributor

Re: Week of the Month (Calculated Column)

Oops my mistake, I misread.

 

WeekStarting = DimDate[Date] - WEEKDAY(DimDate[Date]) + 1

Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
sabeensp Member
Member

Re: Week of the Month (Calculated Column)

@KHorsemanso, it is sort of working, but it is starting from Sunday, our work week starts from Monday through Sunday. I have a week day column 1 through 7 in there.

 

Sorry, I'm new to DAX. I do appreciat eyour help.

sabeensp Member
Member

Re: Week of the Month (Calculated Column)

Thanks, apprecite your help.

KHorseman Super Contributor
Super Contributor

Re: Week of the Month (Calculated Column)

That's an easy enough change. WEEKDAY starts on Sunday by default, but you can change it to start on either Saturday or Monday by changing the second argument.

 

WeekStarting = DimDate[Date] - WEEKDAY(DimDate[Date], 2) + 1

Check out the documentation of WEEKDAY here.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 38 members 1,039 guests
Please welcome our newest community members: