Week Year Representation

[Anonymous]

Hi all,

I need you help in getting the week year representation in such a way that if every week year should be havinf 7 days from Mon-Sun.

For e.g., If we see Dec-2020 dates 28,29,30,31 comes under Week-53 and this is an incomplete week so it also should contain 1,2,3 dates of Jan-2021 into it so that it has total of 7 days and week-1 2021 should start from 4-Jan-2021

Thanks in advance,

[amitchandak · Accepted solution]

@Anonymous , I think my mistake, we need to do it on week year , try this 

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week Year = year([Week Start date])
min week start of year = minx(filter('Date',[Week Year] =earlier([Week Year])),[Week Start date])
week No = quotient(datediff([min week start of year],[date],day),7)+1

[amitchandak]

@Anonymous , Create these columns in date table

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

min week start of year = minx(filter('Date',[Year] =earlier([Year])),[Week Start date])

week No = quotient(datediff([min week start of year],[date],day),7)+1

[Anonymous]

hi @amitchandak ,

It is showing the same as earlier.There is no change

[amitchandak · Accepted solution]

@Anonymous , I think my mistake, we need to do it on week year , try this 

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week Year = year([Week Start date])
min week start of year = minx(filter('Date',[Week Year] =earlier([Week Year])),[Week Start date])
week No = quotient(datediff([min week start of year],[date],day),7)+1

[Anonymous]

hi @amitchandak ,

A small mistake on the requirement that the week should be rolled to the max no days belongs to which year.For e.g., If we take the week from 28-dec-2020 to 3-Jan-2021 maximum no of days are from 2020 so the output should be 53-2020 for those days.

Please help me this is urgent.

Thanks in advance

[amitchandak · Accepted solution]

@Anonymous , Are you looking for ISO week

ISO Week1 = WEEKNUM([Date],21)

[Anonymous]

Thanks @amitchandak , for the swift reply