Forum Discussion
Shubhshrsth143
1 year agoNew Member
Accrual Basis Rows splitting
I have connected database to power bi. i need to calculate accrual amount on the basis of start and expiry date of transaction splitted in month column every month. I have a file that does the same i...
danextian
1 year agoSuper User
Questions:
How big is the data?
Does your table have start and end dates?
How would you split an amount with a start date that isn't 1sth and 15th of the month, say the 5th?
danextian
1 year agoSuper User
Please see this sample M code
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdDNDcAgCAbQXTg3KT8KMovp/mtUbZPipdyEPPGT3kEQEQ6geqKdjFxmUd6iwnV0IBYeXWyBjFMkPEy8uKbIRhpVTaa4KydZCnkeV11TY89TP4HJpKRx5Pm3RlI3IoZtdi0S3V9S9GXibmjfDRG7z7ZH0z5z3Q==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Amount = _t, Start = _t, End = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Amount", Int64.Type}, {"Start", type date}, {"End", type date}}),
#"Inserted Days in Month" = Table.AddColumn(#"Changed Type", "Days in Month", each Date.DaysInMonth([End]), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Days in Month", "Number of Months", each Number.Round(Number.From(([End] - [Start])/( 365.25 / 12 )) ,0 )),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Number of Months", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Period", each {0..[Number of Months]-1}),
#"Expanded Period" = Table.ExpandListColumn(#"Added Custom1", "Period"),
#"Added Custom2" = Table.AddColumn(#"Expanded Period", "Split1", each let
monthly = [Amount]/[Number of Months]
in
if [Period] > 0 and [Period] <[Number of Months]-1 then monthly
else if [Period] = 0 then ( ([Days in Month] - Date.Day([Start]) + 1 )/[Days in Month]) * monthly else
( (Date.Day([End])/[Days in Month]) * monthly )),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Split2", each let
monthly = [Amount]/[Number of Months]
in
if [Period] > 0 and [Period] <[Number of Months]-1 then monthly
else monthly/2),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"Period", Int64.Type}, {"Split1", type number}, {"Split2", type number}})
in
#"Changed Type2"