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/2024quarter start = 31/mar/2019quarter finish = 29/jun/2019 and so on for the 4 quartersfiscal year = FY + yearstandard week number = used add column from example and changed value to week number in power querymonth 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

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

Date table with fiscal week numbers | Microsoft Fabric Community

16 Replies

mwegener
Most Valuable Professional
4 years ago
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)
Anonymous
Not applicable
4 years ago
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
- mwegener
  Most Valuable Professional
  4 years ago
  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"
- mahoneypat
  Microsoft Employee
  4 years ago
  You have a space between __ and FW. Delete that.
  
  Pat
  - Anonymous
    Not applicable
    4 years ago
    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
Anonymous
Not applicable
4 years ago
Have created a week number column and tried to change the letters to upper case and still get the same error massage .

Thanks
Anonymous
Not applicable
4 years ago
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
  Most Valuable Professional
  4 years ago
  Hi Anonymous ,
  
  somehow I misunderstood the requirement, but have a look at the attached file.
  
  Fiscal Week.pbix40 KB
  - Anonymous
    Not applicable
    4 years ago
    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
v-kelly-msft
Community Support
4 years ago
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
  Not applicable
  4 years ago
  Hi v-kelly-msft
  
  Thanks for the reply and it's been sorted by mwegener .
  
  Thanks
Khanna100
Regular Visitor
1 year ago
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))