Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 months ago
Solved

Weeknum as per Calendar

I need each quarter week number, I have filtered = '26-Q1'

 

1-13 Week number starting from Monday

current week = 11 (as per today) 

incase 30th March 2026 is Monday half month (max. days) fall under next week so it will be '26-Q2' and weeknumber = 1

 

I need each quarter week number 1,2,3,4 till 13 and suggest DAX Code ?

 

Thanks!

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 months ago

    Anonymous 

    it's sum ,so it sums up all the weeknum.

    you can try something like this

    Measure = maxx(FILTER('Date','Date'[Date]=today()),'Date'[QuarterWeekNumber])
     
    in my DAX, it shows week 11, if you want to show 10, you need to adjust the DAX coding
     

    what if select other quarter, what's the expected output?

13 Replies

  • danextian's avatar
    danextian
    Super User
    5 months ago

    What if it is the end of the year and 30th of December is a Monday. Will that be long to the last week of the last quarter of the year  or the first week of the following year. It would be easier for us to undersand what you're trying to do if you posted a tabular sample data - with dates, quarters and their accurate week numbers.

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    5 months ago

    Hi Anonymous,

     

    Use below DAX fucntions to add calculated columns in your table

    Week Start Monday

     

    Week Start Monday =
    'Date'[Date] - WEEKDAY('Date'[Date],2) + 1

     

    Quarter Start Date

    Quarter Start =
    DATE(YEAR('Date'[Week Start Monday]),
    (INT((MONTH('Date'[Week Start Monday]) - 1) / 3) * 3) + 1,
    1
    )

     

    Week Number inside Quarter (1-13)

    Quarter Week Number =
    VAR WeekStart = 'Date'[Week Start Monday]
    VAR QuarterStart =
    DATE(
    YEAR(WeekStart),
    (INT((MONTH(WeekStart)-1)/3)*3)+1,
    1
    )
    RETURN
    INT( (WeekStart - QuarterStart) / 7 ) + 1

     

    Quarter Label

     

    Year Quarter =
    FORMAT('Date'[Week Start Monday],"YY") &
    "-Q" &
    FORMAT('Date'[Week Start Monday],"Q")

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 months ago

      I don't have date table and I have calendar table selecting particular column to create calculated column(measure) but getting upnormal result

      • grazitti_sapna's avatar
        grazitti_sapna
        Super User
        5 months ago

        Hi Anonymous ,

         

        I believe you are trying to create a measure, You need to create a calculated column instead.

         

         

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 months ago

    Anonymous 

    not clear about your request, if the week count starts from 2026/1/1, then 3/30 should be week 13. Then it falls to Q2?

    could you pls provide the expected output for the whole year date? And elaborate this.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 months ago

      if Q2 then 1-13 week, Q3 then 1-13 like this

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        5 months ago

        Anonymous 

        pls see if this is what you want

        QuarterWeekNumber =
        VAR ThisDate    = 'Date'[Date]
        VAR ThisQuarter = QUARTER ( ThisDate )    
        VAR ThisYear    = YEAR ( ThisDate )


        VAR a =
            DATE (
                ThisYear,
                1 + ( ThisQuarter - 1 ) * 3,      
                1
            )

        -- First Monday in that quarter
        VAR FirstMondayInQuarter =
            CALCULATE (
                MIN ( 'Date'[Date] ),
                FILTER (
                    ALL ( 'Date' ),
                    'Date'[Date] >= a
                        && 'Date'[Date] < EDATE ( a, 3 )  
                        && WEEKDAY ( 'Date'[Date], 2 ) = 1            
                )
            )
        RETURN
            INT ( ( ThisDate - FirstMondayInQuarter ) / 7 ) + 2
         
        quarter = if ('Date'[QuarterWeekNumber]>13,QUARTER('Date'[Date])+1,QUARTER('Date'[Date]))
         
         
  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    5 months ago

    Hi Anonymous 

    May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.


    Thank you