Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help with Groupings by Contract Week and 4 Weekly Periods

Hi.

 

I have a couple of tables with data that I want to cut by 'Contract Week' and by 'Fixed 4 Week Periods' but I am struggling to understand the best to achieve this.

 

I have been using a table to cross reference the ISO WeekNum with a custom Contract Week number and 4 Week Period but that failed early this year due to Week 52 of 2019 coming through as Week 1 of 2020 therefore skewing the data cross referenced for Week 1 of both 2019 and 2020.

 

I read some people using a combination of YEAR/WEEKNUM but this appears to have caused a different issue as Week 52 of 2019 actually cuts into 2020 again skewing the figures for Week 52 of 2019/2020. 

 

Below is where I am at and I would be grateful for some suggestions on how this could be resolved or if there is a feature of PowerBI I am not aware of that would solve all my issues! 🙂

 

Thanks in advance.

 

Nigel

Current Table

 

 

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    please see the below DAX for Contracted Week 

    ContractedWeek = 
VAR __seq = ( YEAR( 'Table'[Date] ) - YEAR( MIN( 'Table'[Date] ) ) ) * 52
VAR __week = WEEKNUM( 'Table'[Date], 2 ) + __seq
RETURN __week -2

     then the 4WeeklyPeriods should be 

     4WeeklyPeriods= ROUNDUP( 'Table'[ContractedWeek] / 4, 0 )

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn




6 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

     

    Try this column.

    Week seq = 
VAR __seq = ( YEAR( 'Table'[Date] ) - YEAR( MIN( 'Table'[Date] ) ) ) * 52
VAR __week = WEEKNUM( 'Table'[Date] ) + __seq
RETURN INT(  __week / 4 ) + 1

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn


     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Mariusz.

       

      This looks great but is it possible to start from the minimum value of my data table rather i.e. the first Week 4 period starts on ISO Week 2 of 2019?

       

      Also I am still unsure how I can achieve the second column in my example on my first post. i.e. Contract Week 1 which starts at ISO Week 2 but increments beyond the year i.e. Week 1 of 2020 would become Contract Week 52.

       

      I really appreciate your help and apologies If i'm missing something obvious.

       

      Nigel

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Also noticed it's generating a 5 week period. see date table

         

        DATE TABLE