Forum Discussion
How to Create a custom Calendar table - with Week 1 from the 1st Monday of the year
Hi ,
I want to create a calendar table where my custom week starts from 1st Monday of the year.
Example the first Monday for year 2016 is January 4, 2016. The weeks will have to be numbered from 1 starting with Jan 4,2016.
The days of 1 to 3 January 2016 should go to last week of previous year.
I can not use ISOWEEKNUMber as it assumes
Weeks start with Monday. Each week's year is the Gregorian year in which the Thursday falls. The first week of the year, hence, always contains 4 January. ISO week year numbering therefore slightly deviates from the Gregorian for some days close to 1 January.
When I use ISOWEEKNUmber the Weeknumber for 31/12/2018 a Monday comes out as 1.
Any help to achieve this in Power Query will be appreciated.
Cheers
CheenuSing
Hi Anonymous,
Based on my test, we can use the formula to meet your requirement.
new = var a = CALCULATE(COUNT('Table'[Date]),FILTER(ALL('Table'),'Table'[week]=1 && 'Table'[year]=EARLIER('Table'[year]))) return IF(a<7 && 'Table'[week]=1, 52,IF(a<7,'Table'[week]-1,IF(a=7,'Table'[week])))For more details, please check the pbix as attached.
https://www.dropbox.com/s/n7m7zixcz9sqfs5/Calendar%20table.pbix?dl=0
Regards,
Frank
1 Reply
v-frfei-msftCommunity Support
Hi Anonymous,
Based on my test, we can use the formula to meet your requirement.
new = var a = CALCULATE(COUNT('Table'[Date]),FILTER(ALL('Table'),'Table'[week]=1 && 'Table'[year]=EARLIER('Table'[year]))) return IF(a<7 && 'Table'[week]=1, 52,IF(a<7,'Table'[week]-1,IF(a=7,'Table'[week])))For more details, please check the pbix as attached.
https://www.dropbox.com/s/n7m7zixcz9sqfs5/Calendar%20table.pbix?dl=0
Regards,
Frank