cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
irisyeong Regular Visitor
Regular Visitor

List.Dates - how to list duration in months

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: List.Dates - how to list duration in months

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"

 

 

Specializing in Power Query Formula Language (M)
MarcelBeug Super Contributor
Super Contributor

Re: List.Dates - how to list duration in months

Yes, just replace the first line in the AddedDueDates step to:

 

        Table.AddColumn(BufferedSource, "Due Dates", (earlier) => if earlier[#"Months "] = 0 then {earlier[Due Date to Donor]} else List.Generate(

Actually, the sinlge date is also a list (with 1 item) otherwise you would get an error when the nested lists are expanded in the next step.

 

Specializing in Power Query Formula Language (M)
22 REPLIES 22
Super User
Super User

Re: List.Dates - how to list duration in months

That's M (Power Query) code, not DAX. In DAX you could use DATEDIFF:

 

https://msdn.microsoft.com/en-us/library/dn802538.aspx


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


MarcelBeug Super Contributor
Super Contributor

Re: List.Dates - how to list duration in months

In Power Query you can use Date.AddMonths in combination with List.Generate or with a calculation for the number of months required.

 

ListGenerate:

 

let
    Source = #table(type table[StartDate = date, EndDate = date],{
        {#date(2017,10,31),#date(2018,1,1)},
        {#date(2017,10,1),#date(2018,2,10)},
        {#date(2017,12,30),#date(2018,3,30)}}),
    BufferedSource = Table.Buffer(Source),
    AddedDueDates = 
        Table.AddColumn(
            BufferedSource, 
            "Due Dates", 
            (earlier) => List.Generate(
                () => [DueDate = earlier[StartDate], Counter = 1],
                each [DueDate] <= earlier[EndDate],
                each [DueDate = Date.AddMonths(earlier[StartDate],[Counter]),
                      Counter = [Counter] + 1],
                each [DueDate]),
            type {date}),
    ExpandedDueDates = Table.ExpandListColumn(AddedDueDates, "Due Dates")
in
    ExpandedDueDates

 

Calculation alternative 1 (like ListGenerate, the list stops if due date <= end date):

 

let
    Source = #table(type table[StartDate = date, EndDate = date],{
        {#date(2017,10,31),#date(2018,1,1)},
        {#date(2017,10,1),#date(2018,2,10)},
        {#date(2017,12,30),#date(2018,3,30)}}),
    AddedMonths = Table.AddColumn(Source, "Months", (earlier) => {0..12*(Date.Year(earlier[EndDate])-Date.Year(earlier[StartDate]))+Date.Month(earlier[EndDate])-Date.Month(earlier[StartDate])}),
    ExpandedMonths = Table.ExpandListColumn(AddedMonths, "Months"),
    AddedDueDates = Table.AddColumn(ExpandedMonths, "DueDates", each Date.AddMonths([StartDate],[Months]), type date),
    Filtered = Table.SelectRows(AddedDueDates, each [DueDates] <= [EndDate]),
    RemovedMonths = Table.RemoveColumns(Filtered,{"Months"})
in
    RemovedMonths

 

Calculation alternative 2 (additional entry if end date > last due date):

 

let
    Source = #table(type table[StartDate = date, EndDate = date],{
        {#date(2017,10,31),#date(2018,1,1)},
        {#date(2017,10,1),#date(2018,2,10)},
        {#date(2017,12,30),#date(2018,3,30)}}),
    AddedMonths = Table.AddColumn(Source, "Months", each {0..12*(Date.Year([EndDate])-Date.Year([StartDate]))+Date.Month([EndDate])-Date.Month([StartDate])}),
    ExpandedMonths = Table.ExpandListColumn(AddedMonths, "Months"),
    AddedDueDates = Table.AddColumn(ExpandedMonths, "DueDates", each List.Min({[EndDate],Date.AddMonths([StartDate],[Months])}), type date),
    RemovedMonths = Table.RemoveColumns(AddedDueDates,{"Months"})
in
    RemovedMonths
Specializing in Power Query Formula Language (M)
irisyeong Regular Visitor
Regular Visitor

Re: List.Dates - how to list duration in months

I don't think DATEDIFF will work.  

 

This is what I am doing:

 

(a) Screen below shows the original file from Excel

 

Capture1.JPG

 

 

 

 

 

 

 

 

(b) I created a custom column and used List.Date to generate a list of due dates based on the 'months' and 'Due Date to Donor'Capture2.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

(c) The 'custom' column for the 2nd row in the screen below should be 31/12/2015 (12 months after the value in the previous row).  However, it is showing as 12/1/15 because the duration in the listdate formula is in 'days'  

Capture3.JPG

 

 

 

 

 

 

 

 

Is there any way to modify the list.date formula so that the duration is in months?

 

Thanks.

 

 

MarcelBeug Super Contributor
Super Contributor

Re: List.Dates - how to list duration in months

As your post came in, just after mine, you may not have seen my suggested solutions yet.

Specializing in Power Query Formula Language (M)
irisyeong Regular Visitor
Regular Visitor

Re: List.Dates - how to list duration in months

Hi Marcel,

 

Thanks so much for your assistance.

 

I am very new to using Power Query and hence, I am having difficulty understanding your coding. 

 

Which part of your coding should be posted into the 'customr column formula' in Power BI query editor?

 

Thanks.

MarcelBeug Super Contributor
Super Contributor

Re: List.Dates - how to list duration in months

Can you first provide an example of your expectd result, based on your source data?

 

Which date is the first date, how do you determine the number of months (records) required?

 

I assumed you would have some start and end date, but I'm not sure now.

Specializing in Power Query Formula Language (M)
irisyeong Regular Visitor
Regular Visitor

Re: List.Dates - how to list duration in months

Pls see link to the pbi file.

 

https://wvaus-my.sharepoint.com/personal/iris_yeong_worldvision_com_au/_layouts/15/guestaccess.aspx?...

 

 

The last column is a custom column generated using list.date. (The original data has only row 1 for this project and report type)

Row 2-5 have been generated using list.date.

 

The list date formula is calculated using the 'Due Date to Donor' plus 12 months (there is a column called 'Months')

The result in this column  should be 

31/12/14 (keyed in)

31/12/15 

31/12/16

31/12/17

 

There is a column for 'End date'. As the contract end date is 31/5/18, the final end date (31/12/17) should be before the end date 31/5/18. 

 

The custom column in the file does not show the correct dates because it is showing increment of 12 days, instead of 12 months.

This is why my original post is on how to change the duration in list.date from 'day' to 'month.

 

Thanks for your assistance.

MarcelBeug Super Contributor
Super Contributor

Re: List.Dates - how to list duration in months

The link in your previous post is not working.

 

Anyhow, I think the solution with List.Generate is the better option.

 

I adjuted the code to:

 

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,3,30)}}),

    BufferedSource = Table.Buffer(Source),

    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]),
            type {date}),

    ExpandedDueDates = Table.ExpandListColumn(AddedDueDates, "Due Dates")
in
    ExpandedDueDates

 

It is best to paste this code in the advanced query editor, and then adjust the Source step to use your source data,

 

List.Generate has 4 arguments, these are all functions (but don't bother too much about that):

1. Initial value (a record with the first date and the number of months to add in the next step).

2. The condition when to continue looping.

3. The actual action in each iteration.

4. The value that is returned.

 

The step AddedDueDates would look like this:

 

Add custom column using List.Generate.png

 

However, if you would press OK, then an each would be added in front of the first (earlier), which is incorrect and should be removed again.

(Instead of each, (earlier) is used to refer to the other columns in the table, from within the List.Generate function.)

 

Also the last part type {date} would be removed, meaning that you need to change the data type to date after expanding the list column, which is not too bad.

This is the incorrect code after pressing OK on the "Custom Colum" popup.

 

    AddedDueDates = 
        Table.AddColumn(BufferedSource, "Due Dates", each (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])),

Specializing in Power Query Formula Language (M)
MarcelBeug Super Contributor
Super Contributor

Re: List.Dates - how to list duration in months

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
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 25 members 912 guests
Please welcome our newest community members: