Splitting period data into months

[lkarolak]

Hello,

I have my data in the following structure:

ID        BEGIN           END                       VALUE

1         01.10.2017   31.12.2017              90

2         01.01.2016   31.10.2017              1000

....

I need to present this data on a time axis, so that I see the values overlapping and summing up for each month.

Now, I would like to either split the data into more rows, so that the period (Begin-End) will be splitted into single months. E.g. for first row (see above), I would have months: October 2017, November 2017, December 2017. And for each of these rows the original value would be divided by the number of the rows, in this case it would be 90/3=30.

Another approach would be to use the current data structure, but I don't know how to present such time periods (from-until) on a chart. Is it possible at all?

Thank you in advance for your suggestions!

Another approach

[v-qiuyu-msft · Accepted solution]

Hi @lkarolak,

In your scenario, you can create a function use a blank query like below:

(startdate as date, enddate as date) as table =>
let
 //startdate=#date(2015,8,1),
 //enddate=#date(2016,1,31),
 Source = {Number.From(startdate)..Number.From(enddate)},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Calculated End of Month" = Table.TransformColumns(#"Renamed Columns",{{"Date", Date.EndOfMonth, type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Calculated End of Month"),
    #"Inserted End of Year" = Table.AddColumn(#"Removed Duplicates", "EndOfYear", each Date.EndOfYear([Date]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted End of Year", {}, {{"Original", each _, type table}, {"Months_Total", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

 Then rename the function as fnGetAllocationBase.

In the query which get the fact table, invoke this function like below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDTXNTTQRbCNdI1BHEsDpVidaCUjiLiZrqEusnKwEkMDA6CiWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, BEGIN = _t, END = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"BEGIN", type date}, {"END", type date}, {"VALUE", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fnGetAllocationBase([BEGIN],[END])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"BEGIN", "END"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Original", "Months_Total"}, {"Custom.Original", "Custom.Months_Total"}),
    #"Expanded Custom.Original" = Table.ExpandTableColumn(#"Expanded Custom", "Custom.Original", {"Date", "EndOfYear"}, {"Custom.Original.Date", "Custom.Original.EndOfYear"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom.Original", "Value_update", each [VALUE]/[Custom.Months_Total]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.Original.Date", type date}, {"Custom.Original.EndOfYear", type date}})
in
    #"Changed Type1"

Reference:

Allocate Units Based on Dates Using Power Query

Best Regards,
Qiuyun Yu

[WojtekP]

@v-qiuyu-msft 

Great solution!

I tried to use your solution to split total contract value (TCV) into annual contract value (ACV) but with no luck.

My data has a mixture of contracts duration between 1 day to 5years+

All contracts =< 1 year require no attention but if a contract is eg 4 year long, it should be split into 4 rows as follows. Here is the example:

Source:

Index	Amount	Start Date	End Date	Year	Days
1	261,600.00	01/06/2016	31/12/2019	2016	1308

Result:

Index	Amount	Start Date	End Date	Year	Days
1	72,800.00	01/06/2016	31/05/2017	2016	364
1	72,800.00	01/06/2017	31/05/2018	2017	364
1	72,800.00	01/06/2018	31/05/2019	2018	364
1	42,600.00	01/06/2019	31/12/2019	2019	213

So i need to include full 12 months (72,800 USD)  into 2016 as ACV (as opposed to revenue recognition method where i would include only 7 months) ..

Hope it is clear and someone can help! 🙂

[mbdtz]

awesome! exactly what I needed! thanks so much

[Anonymous]

@v-qiuyu-msft

Wow, this is an AWESOME solution!

It helped me very much, thank you Qiuyun!

Please tell me, where can I learn more of those tricks and advanced scenarios working with Power Query.

I mean manipulating tables / using functions, etc.

Would you recommend some site / book / youtube?

Thanks

Michael

[lkarolak]

Thanks Qiuyun Yu, that seems to do the trick!