Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Date table with fiscal week numbers

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 ...
Need Help
  • mwegener's avatar
    mwegener
    4 years ago

    Hi Anonymous ,

     

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

     

Anonymous's avatar
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's avatar
mwegener
Icon for Most Valuable Professional rankMost 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"