Turning dates that span multiple months into multiple records

[Anonymous]

Hi,

I have some data that shows the start and end date for a process, but I was wondering if there was a way to split these dates into monthly intervals. So I could get a line for each month.

Like with the sample data below:

Unique ID	Start Date	End Date
AA	2020-05-05	2020-06-05
AB	2020-04-03	2020-06-19
AC	2020-05-15	2020-05-25

Would turn into:

Unique ID	Start Date	End Date
AA	2020-05-05	2020-05-31
AA	2020-06-01	2020-06-05
AB	2020-04-03	2020-04-30
AB	2020-05-01	2020-05-31
AB	2020-06-01	2020-06-19
AC	2020-05-15	2020-05-25

I was wondering if there is an easy eay to do this in Power Query or DAX? I want to try avoiding doing this in Excel or VBA.

[Fowmy]

@Anonymous 

Please find attached the file which splits the date to ranges as you required.

You can download the file: HERE

I created a function to provide start and end date then it will get you the range in between, then it is a matter of expanding into rows.
More on date splitting in my blog: https://excelfort.com/allocate-amount-monthly-excel-powerquery/

let
    Source = (pStart as date, pEnd as date) =>
let

Source = List.Generate(
                ()=> [fDate = pStart, fDay= if Date.EndOfMonth(fDate) < pEnd then Date.EndOfMonth(fDate) else pEnd ],      
                each [fDate] <= pEnd,
                each 
                let EoM = Date.EndOfMonth( Date.AddMonths([fDate],1)) in 
                [fDate= Date.AddDays(Date.EndOfMonth([fDate]),1) , fDay= if EoM > pEnd then pEnd else Date.EndOfMonth(fDate)]       
        ),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"fDate", "fDay"}, {"fDate", "fDay"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"fDate", type date}, {"fDay", type date}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"fDate", "Date Start"}, {"fDay", "Date End"}})
    in
        #"Renamed Columns"
in

Source

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[ImkeF]

Hi @Fowmy 

it doesn't look as if your results equals the request.

To @Anonymous ,

I've created a function some time ago that creates these kind of intervals: https://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-power-query/

I will just return the end date of each month (or other interval that you need).

But the other columns can be worked out quite easily:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lEyMjAy0DUwBSI4xwzEidUBKnCCi5noGhgjKTC0hChwRjLB0BSJYwQ0IRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Start Date" = _t, #"End Date " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique ID", type text}, {"Start Date", type date}, {"End Date ", type date}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fnDatesBetwen", each fnDatesBetwen([Start Date], [#"End Date "], "Month")),
    #"Expanded fnDatesBetwen" = Table.ExpandListColumn(#"Invoked Custom Function", "fnDatesBetwen"),
    AddStartOfMonth = Table.AddColumn(#"Expanded fnDatesBetwen", "StartOfMonth", each Date.StartOfMonth([fnDatesBetwen])),
    #"Inserted Latest" = Table.AddColumn(AddStartOfMonth, "Start", each List.Max({[StartOfMonth], [Start Date]}), type date),
    #"Inserted Earliest" = Table.AddColumn(#"Inserted Latest", "End", each List.Min({[#"End Date "], [fnDatesBetwen]}), type date),
    Cleanup = Table.SelectColumns(#"Inserted Earliest",{"Unique ID", "Start", "End"})
in
    Cleanup

Please also find the file attached.

[Fowmy · Accepted solution]

Thanks, @ImkeF  for highlighting it, in fact, I have learned a lot from you.
I somehow missed verifying it thoroughly.

I created this function to bring the date range and it works fine. I actually blogged recently https://excelfort.com/allocate-amount-monthly-excel-powerquery/

let
    Source = (pStart as date, pEnd as date) =>
let

Source = List.Generate(
                ()=> [fDate = pStart, fDay= if Date.EndOfMonth(fDate) < pEnd then Date.EndOfMonth(fDate) else pEnd ],      
                each [fDate] <= pEnd,
                each 
                let EoM = Date.EndOfMonth( Date.AddMonths([fDate],1)) in 
                [fDate= Date.AddDays(Date.EndOfMonth([fDate]),1) , fDay= if EoM > pEnd then pEnd else Date.EndOfMonth(fDate)]       
        ),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"fDate", "fDay"}, {"fDate", "fDay"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"fDate", type date}, {"fDay", type date}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"fDate", "Date Start"}, {"fDay", "Date End"}})
    in
        #"Renamed Columns"
in

Source

 
Thanks again

[Greg_Deckler]

@Anonymous - Sure, you can GENERATESERIES and UNION to do that in DAX. I'm sure there is a way to do it in Power Query too @ImkeF .