Forum Discussion

bleow's avatar
bleow
Frequent Visitor
5 years ago
Solved

Generating date table by year

I need to generate a date table like the following:

calendar year startcalendar year end
1/1/202112/12/2021
1/1/202012/12/2020
1/1/201912/12/2019

 

The number of rows is referencing another field:

NumYearsBack

4

 

How do I dynamically generate this date table, accounting for leap years?

 

I have got this so far:

 

= Table.FromList(
        List.Dates(#date(Date.Year(DateTime.LocalNow()), 1, 1), List.First(#"1 Date Start/End"[NumYearsBack])+1, #duration(
            -(   if Number.Mod(Date.Year(DateTime.LocalNow())-1, 4) = 0 and Number.Mod(Date.Year(DateTime.LocalNow())-1, 100) <> 0 then 366 
            else if Number.Mod(Date.Year(DateTime.LocalNow())-1, 4) = 0 and Number.Mod(Date.Year(DateTime.LocalNow())-1, 100) = 0 and Number.Mod(Date.Year(DateTime.LocalNow())-1, 400) = 0 then 366 else 365)
            ,0,0,0)
        )
        , Splitter.SplitByNothing(), null, null, ExtraValues.Error
    )

 

 But the leap year check is wrong because I'm always referencing this year, not the current row's year. Hence I get

 

How do I reference the current row's year? Or, is there a better way of generating this table? Thanks in advance.

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi bleow,

    Did you mean to use the current date and the range to generate a table with the start date and end date? 

    If that is the case, you can take a look at the following formula:

    let
    offset = 4,
    Source =
        List.Transform(
            List.Numbers(
                Date.Year(DateTime.LocalNow())
                - offset
                + 1,
                offset
            ),
            each
                Text.From(_)
                & ","
                & Text.From(#date(_, 1, 1))
                & ","
                & Text.From(#date(_, 12, 31))
        ),
    #"Converted to Table" =
        Table.FromList(
            Source,
            Splitter.SplitTextByDelimiter(","),
            null,
            null,
            ExtraValues.Error
        ),
    #"Renamed Columns" =
        Table.RenameColumns(
            #"Converted to Table",
            {
                {
                    "Column1",
                    "Year"
                },
                {
                    "Column2",
                    "Start"
                },
                {
                    "Column3",
                    "End"
                }
            }
        ),
    #"Changed Type" =
        Table.TransformColumnTypes(
            #"Renamed Columns",
            {
                {
                    "Year",
                    Int64.Type
                },
                {
                    "Start",
                    type date
                },
                {
                    "End",
                    type date
                }
            }
        )
in
    #"Changed Type"

    Regards,
    Xiaoxin Sheng

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Please explain exactly the logic behind the calculations.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi bleow,

    Did you mean to use the current date and the range to generate a table with the start date and end date? 

    If that is the case, you can take a look at the following formula:

    let
    offset = 4,
    Source =
        List.Transform(
            List.Numbers(
                Date.Year(DateTime.LocalNow())
                - offset
                + 1,
                offset
            ),
            each
                Text.From(_)
                & ","
                & Text.From(#date(_, 1, 1))
                & ","
                & Text.From(#date(_, 12, 31))
        ),
    #"Converted to Table" =
        Table.FromList(
            Source,
            Splitter.SplitTextByDelimiter(","),
            null,
            null,
            ExtraValues.Error
        ),
    #"Renamed Columns" =
        Table.RenameColumns(
            #"Converted to Table",
            {
                {
                    "Column1",
                    "Year"
                },
                {
                    "Column2",
                    "Start"
                },
                {
                    "Column3",
                    "End"
                }
            }
        ),
    #"Changed Type" =
        Table.TransformColumnTypes(
            #"Renamed Columns",
            {
                {
                    "Year",
                    Int64.Type
                },
                {
                    "Start",
                    type date
                },
                {
                    "End",
                    type date
                }
            }
        )
in
    #"Changed Type"

    Regards,
    Xiaoxin Sheng