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

 

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

 

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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