powerbipowerquery

PowerBI Create List of Month Dates


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))

Solution

  • 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))