cancel
Showing results for
Did you mean:
Helper III

## How to create Week of quarter calculated column ?

Hi All,

I am struggling to create a calculated column "Week of the quarter". I have quarter-wise date-wise data refer below sample data.

I need a DAX formula to achieved desired result.

 Period IncentiveDate Week of the quarter (desired result) FY20-Q4 01-Jan-20 WK- 1 FY20-Q4 14-Jan-20 WK- 2 FY20-Q4 21-Jan-20 WK- 3 FY20-Q4 28-Jan-20 WK- 4 FY20-Q4 04-Feb-20 WK- 5 FY20-Q4 11-Feb-20 WK- 6 FY20-Q4 18-Feb-20 WK- 7 FY20-Q4 25-Feb-20 WK- 8 FY20-Q4 03-Mar-20 WK- 9 FY20-Q4 10-Mar-20 WK- 10 FY20-Q4 17-Mar-20 WK- 11 FY20-Q4 24-Mar-20 WK- 12 FY20-Q4 31-Mar-20 WK- 13 FY21-Q1 01-Apr-20 WK- 1 FY21-Q1 14-Apr-20 WK- 2 FY21-Q1 21-Apr-20 WK- 3 FY21-Q1 28-Apr-20 WK- 4 FY21-Q1 05-May-20 WK- 5 FY21-Q1 12-May-20 WK- 6 FY21-Q1 19-May-20 WK- 7 FY21-Q1 26-May-20 WK- 8 FY21-Q1 02-Jun-20 WK- 9 FY21-Q1 09-Jun-20 WK- 10 FY21-Q1 16-Jun-20 WK- 11 FY21-Q1 23-Jun-20 WK- 12 FY21-Q1 30-Jun-20 WK- 13

Thanks

Lavdeep

1 ACCEPTED SOLUTION
Super User IV

Week of Qtr =
VAR thisquarter =
QUARTER ( Weeks[IncentiveDate] )
VAR qtrstart =
DATE ( YEAR ( Weeks[IncentiveDate] )thisquarter * 3 - 21 )
VAR wks =
ROUNDUP ( DATEDIFF ( qtrstart, Weeks[IncentiveDate] + 1DAY ) / 70 )
RETURN
"WK-" & wks

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

10 REPLIES 10
Super User IV

This column expression seems to work.

Week of Qtr =
VAR thisquarter =
QUARTER ( Weeks[IncentiveDate] )
VAR qtrstart =
DATE ( YEAR ( Weeks[IncentiveDate] )thisquarter * 3 - 21 )
VAR wks =
DATEDIFF ( qtrstart, Weeks[IncentiveDate], WEEK )
RETURN
IF ( wks = 0"WK-" & wks + 1"WK-" & wks )

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Helper III

Hey Pat, @mahoneypat

When I implement provide DAX formula in my data seems not to working. As you can see below Screenshot where 8th Jan to 11 Jan marked as WK-1 .it should be marked as WK- 2 because of these dates fall under WK-2.

Super User IV

Week of Qtr =
VAR thisquarter =
QUARTER ( Weeks[IncentiveDate] )
VAR qtrstart =
DATE ( YEAR ( Weeks[IncentiveDate] )thisquarter * 3 - 21 )
VAR wks =
ROUNDUP ( DATEDIFF ( qtrstart, Weeks[IncentiveDate] + 1DAY ) / 70 )
RETURN
"WK-" & wks

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Helper III

@mahoneypat  Thanks it's working fine

It is possible if we can achieve the same result without useing "QUARTER " Function ?

Because I also need to use the same Dax formula in power pivot. Since "QUARTER " Function does not exist in the power pivot I am no able to use the same Dax

Super User IV

Yes.  You can use this instead for that variable.

var thisquarter = ROUNDUP(MONTH(Weeks[IncentiveDate])/3,0)

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Solution Specialist

Very neat code.

The only gap is that it is not fixing the sequence if the previous quarter ends and the new quarter begins in the same week.

14th April 2020 should be the 3rd week

Solution Specialist

You can use this idea, add it as a calculated column.

``````QuarterWeekNum =
"WK-"
& SWITCH (
TRUE (),
MONTH ( IncentiveDate ) < 4, WEEKNUM ( IncentiveDate ),
MONTH ( IncentiveDate ) < 7,
IF (
WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 3, 31 ) )
= WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 4, 1 ) ),
WEEKNUM ( IncentiveDate ) - WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 3, 31 ) ) + 1,
WEEKNUM ( IncentiveDate ) - WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 3, 31 ) )
),
MONTH ( IncentiveDate ) < 10,
IF (
WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 6, 30 ) )
= WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 7, 1 ) ),
WEEKNUM ( IncentiveDate ) - WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 6, 30 ) ) + 1,
WEEKNUM ( IncentiveDate ) - WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 6, 30 ) )
),
IF (
WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 9, 30 ) )
= WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 10, 1 ) ),
WEEKNUM ( IncentiveDate ) - WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 9, 30 ) ) + 1,
WEEKNUM ( IncentiveDate ) - WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 9, 30 ) )
)
)``````

Helper III

I use the Dax formula provided by you but its not working for me . Refer below SS yellow highlighted area.

1st, Jan to 7th Jan should be marked as WK-1.

Solution Specialist

Obviously your week definition is not correct in the calendar table you are using.

Week 1 ends on 4th or 5th of Jan 2020 according to your region settings.

If your week begins on Monday then the formula should be modified to:

``````QuarterWeekNum =
"WK-"
& SWITCH (
TRUE (),
MONTH ( IncentiveDate ) < 4, WEEKNUM ( IncentiveDate, 2 ),
MONTH ( IncentiveDate ) < 7,
IF (
WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 3, 31 ), 2 )
= WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 4, 1 ), 2 ),
WEEKNUM ( IncentiveDate, 2 )
- WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 3, 31 ), 2 ) + 1,
WEEKNUM ( IncentiveDate, 2 )
- WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 3, 31 ), 2 )
),
MONTH ( IncentiveDate ) < 10,
IF (
WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 6, 30 ), 2 )
= WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 7, 1 ), 2 ),
WEEKNUM ( IncentiveDate, 2 )
- WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 6, 30 ), 2 ) + 1,
WEEKNUM ( IncentiveDate, 2 )
- WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 6, 30 ), 2 )
),
IF (
WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 9, 30 ), 2 )
= WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 10, 1 ), 2 ),
WEEKNUM ( IncentiveDate, 2 )
- WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 9, 30 ), 2 ) + 1,
WEEKNUM ( IncentiveDate, 2 )
- WEEKNUM ( DATE ( YEAR ( IncentiveDate ), 9, 30 ), 2 )
)
)``````
Frequent Visitor

Hi @lavdeepk ,

Here is an idea to achieve that, but you'd need to see how to adapt the formula in order to manage same week numbers of different quarters. There should be a more efficient way.

Considering a two columns table: "Date" and "Quarter":

``````Week of Quarter =
VAR Week = WEEKNUM('Table'[Date],2)
VAR Week_last_day_Q1 = WEEKNUM(DATE(YEAR('Table'[Date]),3,31))
VAR Week_last_day_Q2 = WEEKNUM(DATE(YEAR('Table'[Date]),6,30))
VAR Week_last_day_Q3 = WEEKNUM(DATE(YEAR('Table'[Date]),9,30))
RETURN
SWITCH('Table'[Quarter],
1 , Week ,
2 , Week - Week_last_day_Q1 ,
3 , Week - Week_last_day_Q2 ,
4 , Week - Week_last_day_Q3
)``````

Jesus.

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors