Help with Groupings by Contract Week and 4 Weekly Periods

[Anonymous]

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

[Mariusz]

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]

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

[Mariusz · Accepted solution]

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]

Great thank you @Mariusz 

That worked perfectly after a slight adjustment to return __week -2 (changed to -1) and that gave me exactly what I need.

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

Thank you for your help.

Nigel

[Anonymous]

Hello,

When I try to use the suggested Measure, Power BI Gives the following error based on the first 'Table'[Date]:

"A single value for column 'Date' in table 'Calendar' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I'm new to this so I'm probably using something wrong.

Any suggestions to point me in the right direction?

Jim

[Anonymous]

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

DATE TABLE