Forum Discussion
Post ProdigyCalculated Column for 445 week approach
Hello everyone!
Is there any chance to get the 445-Week approach into a calculated column without Power Query?
Just from a starting point like the 30.12.2019 start date for the first fiscal week for 2020?
I have found several approaches but all of them are using power query. I am just looking for the calculated column, that shows me the fiscal week and fiscal month.
There are no years with more than 52 weeks. There is a rolling structure.
Well, I got this working.
Step 1; create a date table based on CALENDAR() with the desired start of your fiscal year. I used the 30/12/2019 you gave in your question:
Table = CALENDAR("30/12/2018", "31/12/2020")Then we add a calculated column to it with the following dax:
FiscalWeek = VAR curDate = 'Table'[Date] VAR week = ROUNDUP(DIVIDE(COUNTROWS(FILTER('Table', 'Table'[Date] <= curDate)), 7, 1), 0) VAR fiscalWeek = IF(week >= 53, ROUNDUP(MOD(week, 52.00000001), 0), week) RETURN fiscalWeekThis expression is evaluated for each row in the newly created Date table. Line by line:
VAR curDate = 'Table'[Date] --> A reference to the current row Date value.
VAR week = ROUNDUP(DIVIDE(COUNTROWS(FILTER('Table', 'Table'[Date] <= curDate)), 7, 1), 0) --> From inside out; we count the rows prior to the current row, devide that by 7 and we round it up to the nearest integer.
VAR fiscalWeek = IF(week >= 53, ROUNDUP(MOD(week, 52.00000001), 0), week) --> If the week is bigger than 52, we want to dived it by 52.00001 and round it up. This makes sure that we restart at 1 after week 52 has occured. We divide by 52.00001 because if we divide by 52, the Modulus of 104 is 0 (and we want it to be 1).Let me know if this is what you are looking for! 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
2 Replies
- JarroVGIT
Resident Rockstar
Well, I got this working.
Step 1; create a date table based on CALENDAR() with the desired start of your fiscal year. I used the 30/12/2019 you gave in your question:
Table = CALENDAR("30/12/2018", "31/12/2020")Then we add a calculated column to it with the following dax:
FiscalWeek = VAR curDate = 'Table'[Date] VAR week = ROUNDUP(DIVIDE(COUNTROWS(FILTER('Table', 'Table'[Date] <= curDate)), 7, 1), 0) VAR fiscalWeek = IF(week >= 53, ROUNDUP(MOD(week, 52.00000001), 0), week) RETURN fiscalWeekThis expression is evaluated for each row in the newly created Date table. Line by line:
VAR curDate = 'Table'[Date] --> A reference to the current row Date value.
VAR week = ROUNDUP(DIVIDE(COUNTROWS(FILTER('Table', 'Table'[Date] <= curDate)), 7, 1), 0) --> From inside out; we count the rows prior to the current row, devide that by 7 and we round it up to the nearest integer.
VAR fiscalWeek = IF(week >= 53, ROUNDUP(MOD(week, 52.00000001), 0), week) --> If the week is bigger than 52, we want to dived it by 52.00001 and round it up. This makes sure that we restart at 1 after week 52 has occured. We divide by 52.00001 because if we divide by 52, the Modulus of 104 is 0 (and we want it to be 1).Let me know if this is what you are looking for! 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Hello
I dont think this is a 445 week approach. This DAX expression creates a calendar but broken in 7 weeks increaments.
445 relates to dividing the year 4 13 week periods. P1 = 4 weeks P2 = 4 weeks P3 = 5 then repeats P4 = 4 weeks P5 = 4 weeks P6 = 5 weeks.
There does not seem to be an easy way to do this is DAX.
