topic Re: Calculated Column for 445 week approach in DAX Commands and Tips

Calculated Column for 445 week approach

joshua1990 — Wed, 18 Dec 2019 13:42:57 GMT

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.

Re: Calculated Column for 445 week approach

JarroVGIT — Wed, 18 Dec 2019 19:54:57 GMT

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 fiscalWeek

This 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! 🙂

Re: Calculated Column for 445 week approach

Anonymous — Wed, 13 May 2020 12:12:04 GMT

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.