cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lavdeepk
Resolver I
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.

 

PeriodIncentiveDate      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

Please try this one instead.

 

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

mahoneypat_0-1627735500838.png

 

Pat

 





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

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

10 REPLIES 10
mahoneypat
Super User
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 )

 

mahoneypat_0-1627680208181.png

 

Pat

 





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

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


 

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 

 

lavdeepk_0-1627704402776.png

 

Please try this one instead.

 

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

mahoneypat_0-1627735500838.png

 

Pat

 





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

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

@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 

 

 

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

m_refaei_0-1627680852810.png

14th April 2020 should be the 3rd week

Mohammad_Refaei
Solution Specialist
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 ) )
        )
    )

 

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. 

lavdeepk_0-1627707339336.png

 

 

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

m_refaei_0-1627710467884.png

 

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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors