Generate Customer Calendar using today's date as end date

[themistoklis]

Hello All,

I am using the following dax formula for the creation of the custom calendar

= Source(#date(2017, 01, 01), Duration.Days(#date(2018, 12, 31) - #date(2017, 01, 01))+1, #duration(1, 0, 0, 0))

I would like to replace the end date (#date(2018,12,31) with today's date and i want every time the calendar runs to get today's date as end date.

How can i add today's date in the formula above.

Thanks

[stretcharm · Accepted solution]

This is M language not DAX.

in M you can get today using

DateTime.LocalNow()

https://msdn.microsoft.com/en-us/query-bi/m/datetime-localnow

I use this function for my date dimension from @mattmasson

https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

and pass dates. Setting end date to this will create dates up to today.

= DateFunction(StartDate, Date.AddDays(DateTime.Date(DateTime.LocalNow()),1), null)

[themistoklis]

@stretcharm

@danextian

Indeed im quite new to PowerBI.

Thank you both for the formulas that you sent. They both work great!

My working formula looks like this now:

= Source(#date(2017, 01, 01), Number.From(DateTime.Date(DateTime.LocalNow()) - #date(2017, 01, 01))+1, #duration(1, 0, 0, 0))

[danextian]

Hi @themistoklis,

Source is not a M function. Change your formula to

= List.Dates(#date(2017, 01, 01), Number.From(DateTime.Date(DateTime.LocalNow()) - #date(2017, 01, 01))+1, #duration(1, 0, 0, 0))

Then convert the generated list to a table.

[themistoklis]

@danextian

Source is actually the name of the previous applied step.

And this step has the fucntion that you mentioned (= List.Dates)

Thanks for clarifying it

[danextian · Accepted solution]

Hi @themistoklis,

It seems that you are fairly new to Power BI.

The formula you posted is not that of DAX but of M. If you want to create a calendar in DAX, you may use the CALENDAR() function. Example:

CALENDAR TABLE =
CALENDAR ( "1/1/2018", TODAY () )

The above formula should be created as a table and not a measure or a calculated column.

In M, you will have to create a list of dates first then convert it to a table. An example nested formula that generates a list of dates then converts the list to a table.

= Table.FromList(
List.Dates( #date(2018, 1, 1),  Number.From(DateTime.Date(DateTime.LocalNow())- #date(2018, 1, 1)) + 1, #duration(1, 0, 0, 0)), 
Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error)

From Get Data find Blank Query and then place the formula above into the formula bar. If the formula bar is not visible, go to View tab of Power Query Editor and then tick the checkbox for Formula Bar.

[stretcharm · Accepted solution]

This is M language not DAX.

in M you can get today using

DateTime.LocalNow()

https://msdn.microsoft.com/en-us/query-bi/m/datetime-localnow

I use this function for my date dimension from @mattmasson

https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

and pass dates. Setting end date to this will create dates up to today.

= DateFunction(StartDate, Date.AddDays(DateTime.Date(DateTime.LocalNow()),1), null)