How to make dynamic start date and end date in M language

[Niels_T]

Hello,

I would like to define a dynamic start date and end date in M language from a  blank query.

The start date should always be the beginning of current year and end date should always be the end of current year.

[v-jingzhang]

Hi @Niels_T 

What's your expected result? Do you want to add two columns "Start Date" and "End Date" to an existing table? Or do you want to have a new query which only has Start Date and End Date in one row? Or maybe you want to generate a date column which starts from the Start Date and ends on the End Date?

Best Regards,
Community Support Team _ Jing

[johnt75]

You can use

Date.From( Date.StartOfYear(DateTime.FixedLocalNow()) )
Date.From( Date.EndOfYear(DateTime.FixedLocalNow()) )

[amitchandak]

@Niels_T , You can create two columns like

Date.StartOfYear(DateTime.Date(DateTime.LocalNow()))

Date.EndOfYear(DateTime.Date(DateTime.LocalNow()))

You can also use FixedLocalNow

[Niels_T]

I try to do this from a blank query. I tried doing it like this but it didn't seem to work:

let
Source = Table.AddColumn(Query2, "StartDate", Date.StartOfYear(DateTime.Date(DateTime.LocalNow())))
in
Source

It is stating the following error:

Expression.Error: A cyclic reference was encountered during evaluation.