Generating date table by year

[bleow]

I need to generate a date table like the following:

calendar year start	calendar year end
1/1/2021	12/12/2021
1/1/2020	12/12/2020
1/1/2019	12/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.

[Anonymous · Accepted solution]

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

[amitchandak]

@bleow , add Date.EndOfYear , or have a column with that, that should give you end date.

[Anonymous]

Please explain exactly the logic behind the calculations.