PowerBI Create List of Month Dates

Viewed 342

Hi in powerbi I am trying to create a list of dates starting from a column in my table [COD], and then ending on a set date. Right now this is just looping through 60 months from the column start date [COD]. Can i specify an ending variable for it loop until?

List.Transform({0..60}, (x) => 
Date.AddMonths(
    (Date.StartOfMonth([COD])), x))
3 Answers

Assuming

start=Date.StartOfMonth([COD]),
end = #date(2020,4,30),

One way is to add column, custom column with formula

= { Number.From(start) .. Number.From(end) } 

then expand and convert to date format

or you could generate a list with List.Dates instead, and expand that

= List.Dates(start, Number.From(end) - Number.From(start)+1,  #duration(1, 0, 0, 0))

Assuming you want start of month dates through June 2023. In the example below, I have 2023 and 6 hard coded, but this could easily come from a parameter Date.Year(DateParameter) or or column Date.Month([EndDate]).

Get the count of months with this:

12 * (2023 - Date.Year([COD]) )
+ (6 - Date.Month([COD]) )
+ 1

Then just use this column in your formula:

List.Transform({0..[Month count]-1}, (x) => 
  Date.AddMonths(Date.StartOfMonth([COD]), x) 
)

You could also combine it all into one harder to read formula:

  List.Transform(
    {0..
      (12 * ( Date.Year(DateParameter) - Date.Year([COD]) )
       + ( Date.Month(DateParameter) - Date.Month([COD]) ) 
      )
    }, (x) => Date.AddMonths(Date.StartOfMonth([COD]), x) 
  )

If there is a chance that COD could be after the End Date, you would want to include error checking the the Month count formula.

Generate list:

let
    Start = Date1
    , End = Date2
    , Mos = ElapsedMonths(End, Start) + 1
    , Dates = List.Transform(List.Numbers(0,Mos), each Date.AddMonths(Start, _))
    
in
    Dates

ElapsedMonths(D1, D2) function def:

(D1 as date, D2 as date) =>
    let
        DStart = if D1 < D2 then D1 else D2
        , DEnd = if D1 < D2 then D2 else D1
        , Elapsed = (12*(Date.Year(DEnd)-Date.Year(DStart))+(Date.Month(DEnd)-Date.Month(DStart)))
    in
        Elapsed

Of course, you can create a function rather than hard code startdate and enddate:

(StartDate as date, optional EndDate as date, optional Months as number)=>
    let
        Mos = if EndDate = null
            then (if Months = null
                then error Error.Record("Missing Parameter", "Specify either [EndDate] or [Months]", "Both are null")
                else Months
                )
            else ElapsedMonths(StartDate, EndDate) + 1
        , Dates = List.Transform(List.Numbers(0, Mos), each Date.AddMonths(StartDate, _))
    in
        Dates
Related