Is Today =True - Date column wrong Power Query code.

[elliotstan]

Hi All,
I have a powerquery script to create my date table. Think I copied it from powerpivotpro.com (great site)
Has been working ok but I just realised that the IsToday column is wrong.
Right now it is Wednesday the 9th of September but it thinks today is Tuesday the 9th of September.

below is the code I use

Any help appreciated.

let

    CreateDateTable = (StartDate, EndDate) =>

let

    /*StartDate=#date(2008,1,1),

    EndDate=#date(2019,12,31),*/

    //Create lists of month and day names for use later on

    MonthList = {"January", "February", "March", "April", "May", "June"

                 , "July", "August", "September", "October", "November", "December"},

    DayList = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"},

    //Find the number of days between the end date and the start date

    NumberOfDates = Duration.Days(EndDate-StartDate),

    //Generate a continuous list of dates from the start date to the end date

    DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),

    //Turn this list into a table

    TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}

                     , null, ExtraValues.Error),

    //Caste the single column in the table to type date

    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),

    //Add custom columns for day of month, month number, year

    DayOfMonth = Table.AddColumn(ChangedType, "DayOfMonth", each Date.Day([Date])),

    MonthNumber = Table.AddColumn(DayOfMonth, "MonthNumberOfYear", each Date.Month([Date])),

    Year = Table.AddColumn(MonthNumber, "Year", each Date.Year([Date])),

    DayOfWeekNumber = Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date])+1), 

    //Since Power Query doesn't have functions to return day or month names, 

    //use the lists created earlier for this

    MonthName = Table.AddColumn(DayOfWeekNumber, "MonthName", each MonthList{[MonthNumberOfYear]-1}),

    DayName = Table.AddColumn(MonthName, "DayName", each DayList{[DayOfWeekNumber]-1}),
    

    //Add a column that returns true if the date on rows is the current date

    IsToday = Table.AddColumn(DayName, "IsToday", each Date.IsInCurrentDay([Date]))



in
IsToday,
    #"Invoked FunctionCreateDateTable1" = CreateDateTable(#date(2008, 1, 1), #date(2019, 12, 31)),
    #"Filtered Rows" = Table.SelectRows(#"Invoked FunctionCreateDateTable1", each ([IsToday] = true))
in
    #"Filtered Rows"

 

[ImkeF]

I think this is due to different calendar Settings: The code you've provided would deliver the correct results if the first day of the week would be sunday. But yours is probably monday. So try putting sunday at the end of the DayList.

 

[elliotstan]

Cheers ImkeF!
That seems to have solved it. Thanks so much. Knew it would be something simple.

 

[billszysz]

Hi elliotstan,
I'm late but for further cases you can use the second argument of Date.DayOfWeek function to be sure which day is the first day of week.
So the line should look like this below.

DayOfWeekNumber = Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date], Day.Sunday)+1),

Additionally, you do not need lists (MonthList and DayList) if you use Date.ToText function. In this case you can choose language (culture) as you wish for the name of the month or day

MonthName = Table.AddColumn(DayOfWeekNumber, "MonthName", each Date.ToText([Date], "MMMM", "en-US")),

DayName = Table.AddColumn(MonthName, "DayName", each Date.ToText([Date], "dddd", "en-US")),

So, this part

    MonthList = {"January", "February", "March", "April", "May", "June"

                 , "July", "August", "September", "October", "November", "December"},

    DayList = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"},

is not needed.

Regards

 

[elliotstan]

Cheers for that billszysz
Will have a go at applying those to the code.
Much simpler.