DATESBETWEEN with yearly intervals

[sgupta22]

Hi everyone!

I am trying to create a table using the DatesBetween function where I want the table to have yearly increments. I am using the below formula which gives all the dates between the two dates but I am unable to figure out how to get yearly increments:

Model Dates = DATESBETWEEN('Calendar'[Date], [Model start date], [Model end date])

Thank you for your help!

Cheers

[v-yueyunzh-msft · Accepted solution]

Hi , @sgupta22 

Here are the steps you can refer to :

(1)This is my test data:

Table = CALENDAR( DATE(1988,1,1) , DATE(2023,12,31))

(2)We can create a table:

Table 2 =

var _table=GENERATESERIES( YEAR(MIN('Table'[Date])),YEAR(MAX('Table'[Date])),1)

return

SELECTCOLUMNS(ADDCOLUMNS(_table,"Date",DATE([Value],1,1)-1) ,"Date",[Date])

(3)Then we can meet your need , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Greg_Deckler]

@sgupta22 What do you mean "year increments" ?

[sgupta22]

@Greg_Deckler I mean rather than getting a table which has daily dates between the two dates, I want it to be something like this:

31-Dec-2010

31-Dec-2011

31-Dec-2012

31-Dec-2013

31-Dec-2014

and so on...

Cheers

[Greg_Deckler]

@sgupta22 Try:

Model Dates = 
DISTINCT
  SELECTCOLUMNS(
    SUMMARIZE(
      DATESBETWEEN('Calendar'[Date], [Model start date], [Model end date]),
      'Calendar'[Year],
      '__Date',MAX('Calendar'[Date])
    ),
    "Date",[__Date]
  )
)