cancel
Showing results for
Did you mean:
Resolver I

## 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

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

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!

Resolver I

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

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!

Resolver I

@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

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 ) )
)
)``````

Resolver I

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

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.