How to create Week of quarter calculated column ?

[lavdeepk]

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

[mahoneypat]

This column expression seems to work.

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

Pat

[lavdeepk]

Hey Pat, @mahoneypat  

Thanks for reply 

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.

Please help me out if i am doing anything worng.

You can download this data set to validate the result  

Week of quarter excel file 

[mahoneypat · Accepted solution]

Please try this one instead.

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

Pat

[kattmish]

That was perfect, been looking for that one for a bit.  Thanks @mahoneypat!!

[lavdeepk]

@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 

[mahoneypat]

Yes.  You can use this instead for that variable.

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

Pat

[Mohammad_Refaei]

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

[Mohammad_Refaei]

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

[lavdeepk]

Hi @Mohammad_Refaei 

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. 

[Mohammad_Refaei]

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

[Anonymous]

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.