List of Dates

[BlackBird]

Hi guys!

How can I make a list of dates, more especificaly months, between two dates?

Something like 'ListOfMonths(StartDate;EndDate)'

And if I use it like this

ListOfMonths(01/01/2019;31/12/2019)

the result will be

Months

janeiro-19

fevereiro-19

março-19

abril-19

maio-19

junho-19

julho-19

agosto-19

setembro-19

outubro-19

novembro-19

dezembro-19

[Mariusz]

Hi @BlackBird 

Try this function in Power Query, you will need to paste the below into Blanc Query, you can Invoke by providing start and end dates or add it to your existing table by going to Add Column > Invoke Custom Function and use columns from your table for start and end dates.

(#"Start Date" as date, optional #"End Date" as date ) => let
    enddate = if #"End Date" = null then DateTime.Date( DateTime.LocalNow() ) else  #"End Date", 
    days = Duration.Days( enddate - #"Start Date" ) +1,
    listDates = List.Dates( #"Start Date", days, #duration( 1, 0, 0, 0 ) ),
    monthYear = List.Transform( listDates, each  Date.Year( _ ) * 100 + Date.Month( _ ) ),
    listMonthYear = List.Distinct( monthYear )
    
in
    listMonthYear

Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.

[Tahreem24 · Accepted solution]

@BlackBird ,

Create new table using below DAX formula:

Calendar Table= CALENDAR(DATE(2019,1,1),DATE(2019,12,31))

Don't forget to hit Thumbs up and accept this as a solution if you find it helpful!