Project week calendar

[Anonymous]

Hi all, 

I have a calandar in my power query which counting the weeks correclty, but I am asked to make a new calandar satrting from project start date. 

that means, the porject has been satrted from first of Dec which must be shown as week 0. 

can anyone help me how to make this calandar? 

[v-jingzhang]

Hi @Anonymous 

Does my reply solve your problem? If yes, kindly accept it as the solution and this will help others who have similar questions. Otherwise, please provide more details about your problem so that we can work on it further. Thanks.

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Please try below calendar codes. In this codes, fiscal years start on the 1st of December and end on the 30th of November. Weeks start on Monday. You can modify them per your need. Fiscal Week of Year column is what you want.

let
    // configurations start
    Today=Date.From(DateTime.LocalNow()), // today's date
    FromYear = 2019, // set the start year of the date dimension. 
    ToYear=2021, // set the end year of the date dimension. 
    StartofFiscalYear=12, // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7
    firstDayofWeek=Day.Monday, // set the week's start day, values: Day.Monday, Day.Sunday....
    // configuration end
    FromDate=#date(FromYear,12,1), // dates start from 1st of December of FromYear
    ToDate=#date(ToYear,11,30), // dates end at 30th of November of ToYear
    Source=List.Dates(
        FromDate,
        Duration.Days(ToDate-FromDate)+1,
        #duration(1,0,0,0)
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),
    #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),
    #"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
    #"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Day of Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([Date],firstDayofWeek), Int64.Type),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],firstDayofWeek), Int64.Type),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "Start of Week", each Date.StartOfWeek([Date],firstDayofWeek), type date),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date],firstDayofWeek), type date),
    FiscalMonthBaseIndex=13-StartofFiscalYear,
    adjustedFiscalMonthBaseIndex=if(FiscalMonthBaseIndex>=12 or FiscalMonthBaseIndex<0) then 0 else FiscalMonthBaseIndex,
    #"Added Custom" = Table.AddColumn(#"Inserted End of Week", "FiscalBaseDate", each Date.AddMonths([Date],adjustedFiscalMonthBaseIndex), type date),
    #"Inserted Year1" = Table.AddColumn(#"Added Custom", "Fiscal Year", each Date.Year([FiscalBaseDate]), Int64.Type),
    #"Inserted Quarter1" = Table.AddColumn(#"Inserted Year1", "Fiscal Quarter", each Date.QuarterOfYear([FiscalBaseDate]), Int64.Type),
    #"Inserted Month1" = Table.AddColumn(#"Inserted Quarter1", "Fiscal Month", each Date.Month([FiscalBaseDate]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Month1",{"FiscalBaseDate"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Start Date of Fiscal Year", each if [Month] < StartofFiscalYear then #date([Year]-1,StartofFiscalYear,1) else #date([Year],StartofFiscalYear,1)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Start Date of Week 0", each Date.StartOfWeek([Start Date of Fiscal Year],firstDayofWeek)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Fiscal Week of Year", each Number.RoundDown(Duration.Days([Date] - [Start Date of Week 0]) / 7))
in
    #"Added Custom3"

Let me know if you have any questions.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.