Dynamic Calendar, everything works fine but from List to Table - cant change to Day.Monday

[NovaBI]

Hi all,

I've created a dynamic calendar with a Dataflow. It works fine.

I only have one tiny flaw that i do not like too much. Every time intelligence is based on a date field that is created out of a list.

Per default, Sunday is day 0. I want to see Monday as Day 0 (or 1), so all the following time intelligence is working as well as expected.

So i did change everything concerned to day.monday. But I can not achieve it with the Alternate Day Column. If I use Date.IsinCurrentWeek, it will interprete Sunday as part of this logic.

I think I just need to add the day.monday somewhere as I did it e.g. for the "Start of week". That works fine as a parameter for the Current Week logic, but I do not like this work around.

Find the code and examples attached. Thanks for the help.

 

[amustafa]

Try this..

let
    StartDate = #date(2023, 12, 1),
    EndDate = DateTime.Date(DateTime.LocalNow()),
    Source = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)),
    ConvertToTable = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}),
    ChangedType = Table.TransformColumnTypes(ConvertToTable,{{"Date", type date}}),
    AddYear = Table.AddColumn(ChangedType, "Year", each Date.Year([Date]), type number),
    AddMonthNumber = Table.AddColumn(AddYear, "Month Number", each Date.Month([Date]), type number),
    AddMonthName = Table.AddColumn(AddMonthNumber, "Month Name", each Date.ToText([Date], "MMMM"), type text),
    AddQuarter = Table.AddColumn(AddMonthName, "Quarter", each Date.QuarterOfYear([Date]), type number),
    AddDayOfWeekNumber = Table.AddColumn(AddQuarter, "Day of Week Number", each Date.DayOfWeek([Date], Day.Monday) + 1, type number),
    AddDayOfWeekName = Table.AddColumn(AddDayOfWeekNumber, "Day of Week Name", each Date.ToText([Date], "dddd"), type text),
    AddWeekOfYear = Table.AddColumn(AddDayOfWeekName, "Week of Year", each Date.WeekOfYear([Date], Day.Monday), type number),
    AddFirstDateOfWeek = Table.AddColumn(AddWeekOfYear, "First Date of Week", each Date.StartOfWeek([Date], Day.Monday), type date)
in
    AddFirstDateOfWeek