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"

[Greg_Deckler]

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

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

[Anonymous]

I don't think DATEDIFF will work.  

This is what I am doing:

(a) Screen below shows the original file from Excel

(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'

(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'  

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

Thanks.

[MarcelBeug]

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

[MarcelBeug]

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

[Anonymous]

Hi @MarcelBeug 

Very interesting way of building months list. How does this exactly work?  if instead of 0..12 I place 12 then a get a scalar value of total months - no list. Why? 

{
   0..12 *
            (Date.Year(#date(2018,1,1)) - Date.Year(#date(2017,10, 31)))  ------------------------------ Years
                + (Date.Month(#date(2018,1,1)) - Date.Month(#date(2017,10, 31))) ----------------- Months
}
or otherwise    {0..12* (2018-2017)+(1-10) }

Start	        End
10/31/2017	1/1/2018

Result 

0
1
2
3

[Anonymous]

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]

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.

[Anonymous]

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]

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:

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

[Anonymous]

Thank you.  I managed to replicate what you have done on your screenshots.  I will now attempt to apply your coding to my data.

I really appreciate your assistance.  Thanks.

[MarcelBeug]

You're welcome.

If you use the List.Generate solution: most important is that you need to adjust the code that is generated when using the popup for adding a custom column: remove "each" as explained.

[Anonymous]

I pasted your codes in the advanced editor and changed the source (which is a table in an excel file, saved on Sharepoint) -> see 1st screen. I've circled the new source in red.

I made sure that the fields you referenced to in your coding, exists in my table of data -> see 2nd and 3rd screens.

There is an error message : 'Token Eof expected'.  

Can you pls look at the 1st screen and let me know where I went wrong?  Thanks.

[MarcelBeug]

You should add the code after your Table3020_Table step, before "in".

Then replace Table.Buffer(Source) with Table.Buffer(Table3020_Table).

If required, I can better help you if you supply the code as text, not as a picture.

[Anonymous]

I've pasted the code in 'text'  below.  Would appreciate you can kindly help to amend it.  Thanks.

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]
in
Table3020_Table

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

ExpandedDueDates = Table.ExpandListColumn(AddedDueDates, "Due Dates"),
#"Changed Type" = Table.TransformColumnTypes(ExpandedDueDates,{{"Due Dates", type date}})
in
#"Changed Type"

[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"

[Anonymous]

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[Contract 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}, {"Months ", Int64.Type}})
in
    #"Changed Type"

Hi @MarcelBeug,

The code pasted currently works.  

I am now coming across a scenario, where for a certain report type, the due date is adhoc (I will put '0' for the field 'Months'). I do not want the list.generate to work when the 'months' is zero.  The 'Due Date' should just be equal to 'Due Date to Donor' (which can be a blank field).  There should only be one row (no multiple rows generated) when 'Months' = 0

Is it possibel to have a statement whereby

If "Month" =0, Due Dates = Due Date to Donor, else...the list.generate (as pasted in this post) will apply (i.e. generating a list of dates as long as it is less than the 'end date').

Thanks.

[MarcelBeug · Accepted solution]

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.

[Anonymous]

Thank  you!!!! 🙂

[Anonymous]

Thanks for the tip on how to put in codes. 🙂

I pasted your code.  There is no syntax code (see screen 1).

However, it says that the field 'Months'  was not found (see screen 2).

The field months is in the table (see screen 3).  

So, I don't understand how the error can be rectified.  

Sorry to bother you again...would appreciate your kind assistance to help me trouble shoot this error message.  Thanks heaps.

[Anonymous]

I managed to fix the 'months' issue.  

The list works now.  THANKS SO MUCH FOR YOUR PATIENCE AND YOUR ASSISTANCE!!!!