Date table with fiscal week numbers

[Anonymous]

Hi 

Can i get some advice on how to write a date table with fiscal week number please . our company fiscal week starts on the first week of april and ends on the last week of march the following year. I have import a excel file with the following columns 

date = 31/mar/2019 to 1/apr/2024

quarter start = 31/mar/2019

quarter finish = 29/jun/2019 and so on for the 4 quarters

fiscal year = FY + year

standard week number = used add column from example and changed value to week number in power query

month number = used add column from example and changed value in power query

is there a way to add the fiscal week number as a new column ? tried to use the week number - 12 but the 31/mar/2019 displayed as week 1 and the 1/apr/2019 displayed as week 2 and end up have 53 weeks which should be 52 weeks. I am very new to this and look farword for your help.

Many Thanks

[Khanna100]

This will mark Fiscal Week 53 if April 1st is not the starting day of Fiscal Week 1. You need to isolate the Month Number (to test if it is March (3)).

Fiscal Week =
VAR __fw = [Week Number] - 14 + 1
RETURN IF(__fw<=0,52+__fw,IF(__fw = 1 && 'Date'[Month Number] = 3, 53, __fw))

Update:

I found out that if the calendar year starts on Sunday the above would have the first day of the new fiscal year falling in the last week of the previous fiscal year. So, I came up with this:

Fiscal Week =

VAR __fw = [Week Number] - IF(WEEKDAY(STARTOFYEAR('Date'[Date],1)) = 1, 13, 14) + 1

RETURN IF(__fw<=0,52+__fw,IF(__fw = 1 && 'Date'[Month Number] = 3, 53, __fw))

Update 2024-10-10: 

I found out further anomaly where if the year is a Leap Year and it starts on Sunday, the fiscal week would be wrong in some cases.

Fiscal Week =

VAR __fw = [Week Number] - IF(WEEKDAY(STARTOFYEAR('Date'[Date],1)) = 1, IF(((MOD(YEAR('Date'[Date]),4) = 0 && MOD(YEAR('Date'[Date]),100) <> 0) || MOD(YEAR('Date'[Date]),400) = 0),14,13),14) + 1

RETURN IF(__fw<=0,IF(((MOD(YEAR('Date'[Date]),4) = 0 && MOD(YEAR('Date'[Date]),100) <> 0) || MOD(YEAR('Date'[Date]),400) = 0),53+__fw,52+__fw),IF(__fw = 1 && 'Date'[Month Number] = 3, 53, __fw))

[v-kelly-msft]

Hi  @Anonymous ,

Which day should be the first day of a week?Sunday or Monday?

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Anonymous]

Hi @v-kelly-msft 

Thanks for the reply and it's been sorted by @mwegener .

Thanks

[Anonymous]

Hi @mwegener 

Just tried with the DAX and somehow it only shows the week of 35, Have add column using new column from the data view below is screen shot from BI desktop 

and below is the DAX that i pasted 

Have tried to paste the M code but result is pretty simpler. could you please have a look. 

Thanks

[mwegener]

Hi @Anonymous ,

somehow I misunderstood the requirement, but have a look at the attached file.

[Khanna100]

Thanks MWegener, solved my problem. Just a little modification, to force the first day of the Fiscal year to be the start of the first fiscal week.

Fiscal Week =
VAR __fw = [Week Number] - 14 + 1
RETURN IF(__fw<=0,52+__fw,IF(__fw = 1 && 'Date'[Month Number] = 3, 53, __fw))

Update:

I found out that if the calendar year starts on Sunday the above would have the first day of the new fiscal year falling in the last week of the previous fiscal year. So, I came up with this:

Fiscal Week =

VAR __fw = [Week Number] - IF(WEEKDAY(STARTOFYEAR('Date'[Date],1)) = 1, 13, 14) + 1

RETURN IF(__fw<=0,52+__fw,IF(__fw = 1 && 'Date'[Month Number] = 3, 53, __fw))

Update 2024-10-10: 

I found out further anomaly where if the year is a Leap Year and it starts on Sunday, the fiscal week would be wrong in some cases.

Fiscal Week =

VAR __fw = [Week Number] - IF(WEEKDAY(STARTOFYEAR('Date'[Date],1)) = 1, IF(((MOD(YEAR('Date'[Date]),4) = 0 && MOD(YEAR('Date'[Date]),100) <> 0) || MOD(YEAR('Date'[Date]),400) = 0),14,13),14) + 1

RETURN IF(__fw<=0,IF(((MOD(YEAR('Date'[Date]),4) = 0 && MOD(YEAR('Date'[Date]),100) <> 0) || MOD(YEAR('Date'[Date]),400) = 0),53+__fw,52+__fw),IF(__fw = 1 && 'Date'[Month Number] = 3, 53, __fw))

[Anonymous]

Hi @mwegener 

Apologies for the late reply as I can only learn this over the weekend . 

I have watched a video on youtube and foloowed steps and done a calander and used your DAX code to get the FW numbers sorted . 

below is the link that i watched 

Date Dimension in Power BI with Financial or Fiscal Columns - YouTube

will have a play around and see if anything else needs to add . 

Many thanks for your help and much appreciated .

Cheers

[mwegener · Accepted solution]

Hi @Anonymous ,

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

[Anonymous]

HI I HAVE THE SAME QUESTION, but i cant view the solution? please thanks

[Anonymous]

the formula i need is for power query not power bi please

[Anonymous]

Have created a week number column and tried to change the letters to upper case and still get the same error massage .

Thanks

[Anonymous]

Hi @mwegener 

Thanks for the reply and i have tried but there is an error message says

Token Eof expected and not sure what happened as i did copy your code & pasted in. Screen shot below.

Thanks

[mahoneypat]

You have a space between __ and FW.  Delete that.

Pat

[Anonymous]

Hi @mahoneypat 

There is a error message when delete to space 

Maybe somewhere along the line that i've done wrong but just trying to have a calander that will have the date,fiscal week,fiscal quarter & fiscal year on it. 

Thanks

[mwegener]

Hi @Anonymous ,

the code is not M (Power Query), but DAX.

Maybe this helps

let
    Source = List.Range({0..52}, 1),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Week Number"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Week Number", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Fiscal Week Number", each if [Week Number] - 32 + 1 <= 0 then 52 + [Week Number] - 32 + 1 else [Week Number] - 32 + 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Fiscal Week Number", Int64.Type}})
in
    #"Changed Type1"

[mwegener]

Hi @Anonymous ,

take a look at this solution.

https://community.powerbi.com/t5/Desktop/Creating-a-fiscal-week-column/m-p/556549

DAX:

Fiscal Week = 
VAR __fw = [WeekNum] - 32 + 1
RETURN IF(__fw<=0,52+__fw,__fw)