M query to expand table not including first month

[Anonymous]

Hello,

A few months ago someone helpfully provided me with the M query below. This was designed to expand a table to include a row per month of activity between the designated dates. I have adapted the query below, but for some reason, the first month of absence is not included. Can anyone fix the query or suggest an alternative solution?

(#"Absence Start Date" as date, #"Absence End Date" as date ) => let
    enddate = if #"Absence End Date" = null then DateTime.Date( DateTime.LocalNow() ) else  #"Absence End Date", 
    days = Duration.Days( enddate - #"Absence Start Date" ),
    listDates = List.Dates( #"Absence Start Date", days, #duration( 1, 0, 0, 0 ) ),
    firstDates = List.Select( listDates, each Date.Day( _ ) = 1 ),
    monthYear = List.Transform( firstDates, each Text.Start( Date.MonthName( _ ), 3 ) & "- " & Text.End( Number.ToText( Date.Year( _ ) ), 2  ) )
    
in
    monthYear

Tiff

[Mariusz · Accepted solution]

Hi @Anonymous 

It looks like my work , please see the adjusted function below.

(#"Absence Start Date" as date, #"Absence End Date" as date ) => let
    enddate = if #"Absence End Date" = null then DateTime.Date( DateTime.LocalNow() ) else  #"Absence End Date", 
    days = Duration.Days( enddate - #"Absence Start Date" ) + 1,
    listDates = List.Dates( #"Absence Start Date", days, #duration( 1, 0, 0, 0 ) ),
    monthYear = List.Transform( listDates, each Text.Start( Date.MonthName( _ ), 3 ) & "- " & Text.End( Number.ToText( Date.Year( _ ) ), 2  ) ),
    distMonthYear = List.Distinct( monthYear )
    
in
    distMonthYear

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

That's worked perfectly, thanks so much for your help then and now!