cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

How to spread value over month period not day period

I have data with contract values that need to be spread over the length of a contract in PowerQuery. I found a solution online that used {[Start Date]..[End Date]} - but that's giving me every single day, whereas I need it on a monthly basis. I tried using Date.Month([Start Date]) but then it didn't count the time over different years.

 

For example,

 

Start DateEnd DateValueNumber of Months
01/12/202003/12/20201004

 

I would like to become this so I can spread the 100 over the 4 months to be 25 each.

 

Start DateEnd DateValueNumber of MonthsCustom Date
01/12/202003/12/2020100401/12/2020
01/12/202003/12/2020100401/01/2021
01/12/202003/12/2020100401/02/2021
01/12/202003/12/2020100401/03/2021
2 ACCEPTED SOLUTIONS
Super User III
Super User III

Don't @RickmasPick

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzTSNzIwMlDSUQKyDIxBHEMQxwAkZKIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, Value = _t, #"Number of Months" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Value", Int64.Type}, {"Number of Months", Int64.Type}}),

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Select(List.Dates([Start Date],Number.From([End Date]-[Start Date])+1,#duration(1,0,0,0)), each Date.Day(_) =1)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
    #"Changed Type1"

Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

Bless you

SU18_powerbi_badge

View solution in original post

Super User III
Super User III

@RickmasPick 

It does start on the first month, as long as the start date is the first day of the month. If you want to add the first month even if the start date is not the first day of the month, just use Date.StartOfMonth([Start Date]) in the List.Dates

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzTSNzIwMlDSUQKyDIxBHEMQxwAkZKIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, Value = _t, #"Number of Months" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Value", Int64.Type}, {"Number of Months", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Select(List.Dates(Date.StartOfMonth([Start Date]),Number.From([End Date]-[Start Date])+1,#duration(1,0,0,0)), each Date.Day(_) =1)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
    #"Changed Type1"

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

View solution in original post

5 REPLIES 5
Super User III
Super User III

@RickmasPick 

It does start on the first month, as long as the start date is the first day of the month. If you want to add the first month even if the start date is not the first day of the month, just use Date.StartOfMonth([Start Date]) in the List.Dates

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzTSNzIwMlDSUQKyDIxBHEMQxwAkZKIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, Value = _t, #"Number of Months" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Value", Int64.Type}, {"Number of Months", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Select(List.Dates(Date.StartOfMonth([Start Date]),Number.From([End Date]-[Start Date])+1,#duration(1,0,0,0)), each Date.Day(_) =1)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
    #"Changed Type1"

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

View solution in original post

@AlB thank you!! 😀

Super User III
Super User III

Don't @RickmasPick

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzTSNzIwMlDSUQKyDIxBHEMQxwAkZKIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t, Value = _t, #"Number of Months" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Value", Int64.Type}, {"Number of Months", Int64.Type}}),

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Select(List.Dates([Start Date],Number.From([End Date]-[Start Date])+1,#duration(1,0,0,0)), each Date.Day(_) =1)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
    #"Changed Type1"

Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

Bless you

SU18_powerbi_badge

View solution in original post

@AlB  oh wait sorry my only question is, it starts from the second month, how do I get it to start from the original month i.e 01/12/2020 - 01/03/2021 (starts December 2020 and ends in March 2021)?

@AlB thank you so much!!!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors