Date table - Fiscal week start Saturday?

Power Query
1

Hello. I’m trying to create an extended date table that starts Fiscal week on Saturday. Can anyone share guidance on how I might achieve this?

1 Like
2

Hi @tigerec,

Welcome to the forum!

I would suggest checking the documentation because there are a number of Date related M functions that take an optional firstDayOfWeek parameter. Like this one for example.

Without knowing any of the specifics in regards to your Custom Fiscal Week logic - it’ll be difficult to provide further guidance. If you know how to create that FW calculation in Excel or DAX that would be helpful for anyone here trying to provide support, should you need further assistance.

1 Like
3

Thanks for the response. Sorry, I didn’t specify - this was actually in reference to your extended date table. I was wondering if there was something I could do to adjust it.

4

Hi @tigerec,

Yes. Changes to these two variables are required.

StartOfWeekDayName = Text.Proper( Text.Start( Date.DayOfWeekName( #date(2021, 3, 6)), 3)),  //Enter a date that is a firstDayOfWeek

and

AddFYDateRange = Table.Buffer( Table.ExpandTableColumn( Table.ExpandTableColumn( Table.AddColumn( Table.Group( Table.Group( Table.AddColumn( Table.AddColumn( 
  Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( { Number.From(FYCalendarStart)..Number.From(EndDate) }, Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "Date"}}), 
    "FiscalFirstDay", each if Date.Month([Date]) < FYStartMonth then #date( Date.Year([Date]), FYStartMonth, 1) else #date( Date.Year([Date])+1, FYStartMonth, 1)),
    "FWStartDate", each  Date.AddYears(Date.StartOfWeek( [Date], Day.Saturday), 1)), // Set optional firstDayOfWeek 
    {"FiscalFirstDay", "FWStartDate"}, {{"AllRows", each _, type table [Date=nullable date, FiscalFirstDay=date, FWStartDate=date]}}),
    {"FiscalFirstDay"}, {{"AllRows2", each _, type table [FiscalFirstDay=date, FWStartDate=date, AllRows=table]}}),
    "Custom", each Table.AddIndexColumn( [AllRows2], "FY Week", 1, 1))[[Custom]],
    "Custom", {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}, {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}),
    "AllRows", {"Date"}, {"Date"})[[Date], [FY Week]]
  ),

Please verify if this has the desired effect.
I hope this is helpful

1 Like
5

Ummm, okay - who do I make out the check to?? :astonished:

6

Hi @tigerec! Welcome to the Forum! It’s great to know that you are having a great experience so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

7

:open_mouth: that was a bad mistake… I’ll change that, thanks!

Will add taking an english language course to my list
but it might be a while before I get around to it…