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 tab...
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




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