Forum Discussion

Sheelaaryal's avatar
Sheelaaryal
Frequent Visitor
2 years ago
Solved

Weeknum for accounting period

How to calculate the week number based on the accounting periods, I have accounting periods 1, 2, 3, ...12 in my data and I am looking to calculate the number of weeks in each accounting period.

 

Example:

Accounting Period 1 

Week 1,

Week 2,

Week 3,

Week 4

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    is this what you want?

     

    weeknum =
    var _first=CALCULATE(min('Table'[Date]),ALLEXCEPT('Table','Table'[accountin period]))
    return rounddown(('Table'[Date]-_first)/7,0)+1
     
     
    pls see the attachment below

     

6 Replies

  • Sheelaaryal's avatar
    Sheelaaryal
    Frequent Visitor
    2 years ago

    Sure, I have accounting period and date column in my data like below and for output I am looking for Weeknum restarting at each accounting period like WeekNum Column

     

    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      2 years ago

      is this what you want?

       

      weeknum =
      var _first=CALCULATE(min('Table'[Date]),ALLEXCEPT('Table','Table'[accountin period]))
      return rounddown(('Table'[Date]-_first)/7,0)+1
       
       
      pls see the attachment below

       

    • Sheelaaryal's avatar
      Sheelaaryal
      Frequent Visitor
      2 years ago

      Also I used this  and this works for some of the periods and does not for some

       

      WeekNum = RANKX(FILTER('Table',[Period]=EARLIER('Table'[Period])),WEEKNUM([Date],2),,ASC,Dense)