List.Dates - how to list duration in months

[Anonymous]

I used the following DAX formula to create a custom column to generate the list of due dates of reports.  

List.Dates([StartDate], [DateDifference]+1, #duration(1,0,0,0))

The number '1' in duration is for'days'.  I have a separate column for the 'number of months' which I will use to determine the next reporting date.  How do I change the duration in the formula from 'days' to 'months'?

Thanks.

[MarcelBeug · Accepted solution]

Below your code, adjusted according to the remarks in my previous post.

And outlined.

(If you paste code on this forum, you can use the button "Insert Code" (left from the smiley), which results in better layout).

I can't test of course, but it should work. At least no syntax errors are reported.

let
    Source = SharePoint.Files("https://wvaus-my.sharepoint.com/personal/iris_yeong_worldvision_com_au/", [ApiVersion = 15]),
    #"PPP Income Pipeline and Compliance Register TEST_10Jan18 xlsm_https://wvaus-my sharepoint com/personal/iris_yeong_worldvision_com_au/Documents/Power BI/" = Source{[Name="PPP Income Pipeline and Compliance Register TEST_10Jan18.xlsm",#"Folder Path"="https://wvaus-my.sharepoint.com/personal/iris_yeong_worldvision_com_au/Documents/Power BI/"]}[Content],
    #"Imported Excel" = Excel.Workbook(#"PPP Income Pipeline and Compliance Register TEST_10Jan18 xlsm_https://wvaus-my sharepoint com/personal/iris_yeong_worldvision_com_au/Documents/Power BI/"),
    Table3020_Table = #"Imported Excel"{[Item="Table3020",Kind="Table"]}[Data],

    BufferedSource = Table.Buffer(Table3020_Table),
    AddedDueDates =
    Table.AddColumn(
        BufferedSource,
        "Due Dates",
        (earlier) => List.Generate(
            () => [DueDate = earlier[Due Date to Donor], Counter = earlier[Months]],
            each [DueDate] <= earlier[End date],
            each [DueDate = Date.AddMonths(earlier[Due Date to Donor],[Counter]),
                  Counter = [Counter] + earlier[Months]],
            each [DueDate])),
    ExpandedDueDates = Table.ExpandListColumn(AddedDueDates, "Due Dates"),
    #"Changed Type" = Table.TransformColumnTypes(ExpandedDueDates,{{"Due Dates", type date}})
in
    #"Changed Type"

[v-chuncz-msft]

@Anonymous,

By the way, you may help accept the useful solution. Your contribution is highly appreciated.

[MarcelBeug]

An alternative solution with calculations.

The 3rd example initially gets the last due date on June 30, 2019, but that will be filtered out as it is after the End date (June 1, 2019). This situation only occurs if the last due date is in the same month as the end date.

let
    Source = #table(type table[Due Date to Donor = date, Months = Int64.Type, End date = date],{
        {#date(2014,12,31),12,#date(2018,5,31)},
        {#date(2017,10,1),3,#date(2018,8,10)},
        {#date(2017,12,30),6,#date(2019,6,1)}}),
    AddedPeriodInMonths = Table.AddColumn(Source,"PeriodInMonths", each 12*(Date.Year([End date])-Date.Year([Due Date to Donor]))+Date.Month([End date])-Date.Month([Due Date to Donor])),
    AddedNoOfDonations = Table.AddColumn(AddedPeriodInMonths, "NoOfDonations", each Number.IntegerDivide([PeriodInMonths], [Months]), Int64.Type),
    AddedMonths = Table.AddColumn(AddedNoOfDonations, "DonationMoments", each {0..[NoOfDonations]}),
    ExpandedMonths = Table.ExpandListColumn(AddedMonths, "DonationMoments"),
    AddedDueDates = Table.AddColumn(ExpandedMonths, "DueDates", each Date.AddMonths([Due Date to Donor],[Months]*[DonationMoments])),
    Filtered = Table.SelectRows(AddedDueDates, each [DueDates] <= [End date]),
    RemovedColumns = Table.RemoveColumns(Filtered,{"PeriodInMonths", "NoOfDonations", "DonationMoments"}),
    Typed = Table.TransformColumnTypes(RemovedColumns,{{"DueDates", type date}})
in
    Typed