Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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)

View solution in original post

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)

View solution in original post

22 REPLIES 22
Greg_Deckler
Super User
Super User

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

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

 

 

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

Specializing in Power Query Formula Language (M)

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)

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
Not applicable

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.

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)
Anonymous
Not applicable

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.

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)
Anonymous
Not applicable

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.

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.

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

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.


Capture4.JPGCapture5.JPGCapture6.JPG

 

 

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.

Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

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"

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)
Anonymous
Not applicable

 

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.

 

 

 

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)
Anonymous
Not applicable

Thank  you!!!! 🙂

 

Anonymous
Not applicable

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.

 

 

Capture7.JPGCapture8.JPGCapture9.JPG

Anonymous
Not applicable

I managed to fix the 'months' issue.  

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.