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

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
AlB
Super User
Super User

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
Super User
Super User

@Anonymous 

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
AlB
Super User
Super User

@Anonymous 

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

 

Anonymous
Not applicable

@AlB thank you!! 😀

AlB
Super User
Super User

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

Anonymous
Not applicable

@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)?

Anonymous
Not applicable

@AlB thank you so much!!!

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.