Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have a set of data related to amortization of costs that looks like below:
Unique ID | Fiscal Year | Posting Period | Vendor | Amount in local currency | Start Fiscal Month | Start Fiscal Year | End Fiscal Month | End Fiscal Year | Amortization Period in months | Per Period Amortization Amount | Jul'22 | Aug'22 | Sep'22 | Oct'22 | Nov'22 | Dec'22 | Jan'23 | Feb'23 | Mar'23 | Apr'23 | May'23 | Jun'23 | Current FY Amortization Months Planned | No of Months Amortized | No of Amortization Months Outstanding | Months greater than 12 | Current Amount | Non Current Amount | Actual Prepaid Exp Balance |
400074039_5100045667_008_KR | 2023 | 5 | 400074039 | 3499.12 | 4 | 2023 | 3 | 2024 | 12 | 291.59 | 0 | 0 | 0 | 291.59 | 291.59 | 291.59 | 291.59 | 291.59 | 291.59 | 291.59 | 291.59 | 291.59 | 9 | 6 | 6 | 0 | 1749.58 | 0 | 1749.58 |
Unfortunately the monthly amortization amount is shown only for the months in the current fiscal year (2023). However, the amortization extends into future fiscal years (2024 in the sameple above). The months in the next financial year are shown in End Fiscal Month and End Fiscal Year. In this case month 4 for 2024. The full period of the amortization (12 months in this case) is in Period in Months. The monthly amortization amount is shown in Per Period Amortization Amount.
I would like to create visualizations and do other analysis that requires that the months be extended to include the future fiscal years with the correct amount entered for each of the future fiscal year months.
Does anybody have any suggestions? I have tried unpivoting the data and then I thought I could do some sort of fill-down but am stuck as the number of lines to fill down by depends on how many months of amortization exist for how many future months/future years. The unpivoted data looks like this:
Unique ID | Start Fiscal Month | Start Fiscal Year | End Fiscal Month | End Fiscal Year | Current FY Amortization Months Planned | No of Months Amortized | No of Amortization Months Outstanding | Attribute | Value |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Jul'22 | 0 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Aug'22 | 0 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Sep'22 | 0 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Oct'22 | 291.59 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Nov'22 | 291.59 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Dec'22 | 291.59 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Jan'23 | 291.59 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Feb'23 | 291.59 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Mar'23 | 291.59 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Apr'23 | 291.59 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | May'23 | 291.59 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Jun'23 | 291.59 |
If I merge in a date table I get the extra rows but need a way to complete only those rows that have valid amortization amounts in the future years/months:
Unique ID | Start Fiscal Month | Start Fiscal Year | End Fiscal Month | End Fiscal Year | Current FY Amortization Months Planned | No of Months Amortized | No of Amortization Months Outstanding | Month.1 | Month.2 | Amortization | Month.1 Full | Fiscal Month Number | Fiscal Year Month Number |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Jul | 2022 | 0 | July | 1 | 202301 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Aug | 2022 | 0 | August | 2 | 202302 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Sep | 2022 | 0 | September | 3 | 202303 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Oct | 2022 | 291.59 | October | 4 | 202304 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Nov | 2022 | 291.59 | November | 5 | 202305 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Dec | 2022 | 291.59 | December | 6 | 202306 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Jan | 2023 | 291.59 | January | 7 | 202307 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Feb | 2023 | 291.59 | February | 8 | 202308 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Mar | 2023 | 291.59 | March | 9 | 202309 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Apr | 2023 | 291.59 | April | 10 | 202310 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | May | 2023 | 291.59 | May | 11 | 202311 |
400074039_5100045667_008_KR | 4 | 2023 | 3 | 2024 | 9 | 6 | 6 | Jun | 2023 | 291.59 | June | 12 | 202312 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202401 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202402 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202403 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202404 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202405 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202406 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202407 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202408 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202409 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202410 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202411 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202412 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202501 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202502 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202503 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202504 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202505 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202506 |
null | null | null | null | null | null | null | null | null | null | null | null | null | 202507 |
Does anybody have any ideas or pointers?
Solved! Go to Solution.
No. Should be doable just using a slightly different year calculation.
--UPDATE-- @vgeldbr Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jci7DcAgDIThXVxH6PyCeIZ0aRHy/ltgkuJ0n/45yQAMg0Y6F817Hwnc+bx0kVpEYylZTSB64k8/9+Xg5kFrbQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Amount in local currency" = _t, #"Start Fiscal Month" = _t, #"Start Fiscal Year" = _t, #"End Fiscal Month" = _t, #"End Fiscal Year" = _t, #"Amortization Period in months" = _t, #"Per Period Amortization Amount" = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"Amount in local currency", type number}, {"Start Fiscal Month", Int64.Type}, {"Start Fiscal Year", Int64.Type}, {"End Fiscal Month", Int64.Type}, {"End Fiscal Year", Int64.Type}, {"Amortization Period in months", Int64.Type}, {"Per Period Amortization Amount", type number}}),
addRowNumber = Table.AddColumn(chgTypes, "rowNumber", each {0..[Amortization Period in months] - 1}),
expandRowNumber = Table.ExpandListColumn(addRowNumber, "rowNumber"),
addPeriod =
Table.AddColumn(
expandRowNumber,
"period",
each let
year = Text.From([Start Fiscal Year] + (Number.RoundUp(([Start Fiscal Month] + [rowNumber]) / 12) - 1)),
month = Text.PadStart(Text.From(Number.Mod([Start Fiscal Month] + [rowNumber] - 1, 12) + 1), 2, "0")
in
Number.From(Text.Combine({year, month}))),
remOthCols = Table.SelectColumns(addPeriod,{"Unique ID", "Start Fiscal Month", "End Fiscal Month", "Per Period Amortization Amount", "period"})
in
remOthCols
To get this:
Pete
Proud to be a Datanaut!
Hi @vgeldbr ,
The below query turns this:
...into this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEwMDA3MTC2jDc1BDJNTM3MzOMNDCzivYOUdJSMTSwt9QyNgCwTIDYyMDIGCUKYIBGwlJGloZ6ppVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Amount in local currency" = _t, #"Start Fiscal Month" = _t, #"Start Fiscal Year" = _t, #"End Fiscal Month" = _t, #"End Fiscal Year" = _t, #"Amortization Period in months" = _t, #"Per Period Amortization Amount" = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"Amount in local currency", type number}, {"Start Fiscal Month", Int64.Type}, {"Start Fiscal Year", Int64.Type}, {"End Fiscal Month", Int64.Type}, {"End Fiscal Year", Int64.Type}, {"Amortization Period in months", Int64.Type}, {"Per Period Amortization Amount", type number}}),
addDtStartMonth = Table.AddColumn(chgTypes, "dtStartMonth", each Date.StartOfMonth(
#date(
[Start Fiscal Year],
[Start Fiscal Month],
01
)
)),
addDtEndMonth = Table.AddColumn(addDtStartMonth, "dtEndMonth", each Date.EndOfMonth(
#date(
[End Fiscal Year],
[End Fiscal Month],
01
)
)),
addMonthList =
Table.AddColumn(
addDtEndMonth,
"monthList",
each List.Distinct(
List.Transform(
{ Number.From([dtStartMonth])..Number.From([dtEndMonth]) },
each Date.StartOfMonth(Date.From(_))
)
)
),
expandMonthList = Table.ExpandListColumn(addMonthList, "monthList"),
remOthCols = Table.SelectColumns(expandMonthList,{"Unique ID", "Start Fiscal Month", "Start Fiscal Year", "End Fiscal Month", "End Fiscal Year", "Per Period Amortization Amount", "monthList"})
in
remOthCols
It looks pretty big and complicated, but in summary, it's just:
-1- addDtStartMonth = Convert your start month/year into a proper date
-2- addDtEndMonth = Convert your end month/year into a proper date
-3- addMonthLisy = Create a list of dates between these two
-4- expandMonthList = Expand the list of dates to new rows
I imagine you'll need to do a bit of jiggery-pokery with the date calculations so they work as fiscal periods rather than calendar months, but this basic structure is the way to go, I think.
Pete
Proud to be a Datanaut!
@BA_Pete this is fantastic. Thanks!
I'm stuck on taking this further as our real world is a bit more complex. We use fiscal months not calendar months (544 week model). I have a standard date table I can use.
In our world Fiscal Year runs from July to June with Month 1 = July and Month 11 = June. But the 544 week model means the fiscal months do not fall into the calendar months.
What I am unable to figure out now is in this step:
addMonthList = Table.AddColumn( addDtEndMonth, "monthList", each List.Distinct( List.Transform( { Number.From([dtStartMonth])..Number.From([dtEndMonth]) }, each Date.StartOfMonth(Date.From(_)) ) ) ),
I need to generate a list of fiscal months not calendar months. I thought I could just generate the YearMonths (e.g. 202303, 202304 ... 202402) and then use my date table to join the actual months but nothing I've tried works. I must be missing some magic in how the numbers are converted back to dates.
Any further thoughts/ideas?
Hi @vgeldbr ,
Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEwMDA3MTC2jDc1BDJNTM3MzOMNDCzivYOUdJSMTSwt9QyNgCwTIDYyMDIGCUKYIBGwlJGloZ6ppVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Amount in local currency" = _t, #"Start Fiscal Month" = _t, #"Start Fiscal Year" = _t, #"End Fiscal Month" = _t, #"End Fiscal Year" = _t, #"Amortization Period in months" = _t, #"Per Period Amortization Amount" = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"Amount in local currency", type number}, {"Start Fiscal Month", Int64.Type}, {"Start Fiscal Year", Int64.Type}, {"End Fiscal Month", Int64.Type}, {"End Fiscal Year", Int64.Type}, {"Amortization Period in months", Int64.Type}, {"Per Period Amortization Amount", type number}}),
addRowNumber = Table.AddColumn(chgTypes, "rowNumber", each {0..[Amortization Period in months] - 1}),
expandRowNumber = Table.ExpandListColumn(addRowNumber, "rowNumber"),
addPeriod =
Table.AddColumn(
expandRowNumber,
"period",
each let
year = Text.From(if [Start Fiscal Month] + [rowNumber] > 12 then [End Fiscal Year] else [Start Fiscal Year]),
month = Text.PadStart(Text.From(Number.Mod([Start Fiscal Month] + [rowNumber] - 1, 12) + 1), 2, "0")
in
Number.From(Text.Combine({year, month}))
),
remOthCols = Table.SelectColumns(addPeriod,{"Unique ID", "Start Fiscal Month", "Start Fiscal Year", "End Fiscal Month", "End Fiscal Year", "Amortization Period in months", "Per Period Amortization Amount", "period"})
in
remOthCols
To get this output:
Pete
Proud to be a Datanaut!
@BA_Pete so close!! It works perfectly where amortization spreads only from current year to the next year but if the amortization is over a longer period (e.g. 2021 to 2024) then it breaks. I guess I need to generate a separate list of fiscal years between the Start Fiscal Year and the End Fiscal Year with the Fiscal Months separate and then merge them to form the period (ie. 202107 to 202409).
No. Should be doable just using a slightly different year calculation.
--UPDATE-- @vgeldbr Try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jci7DcAgDIThXVxH6PyCeIZ0aRHy/ltgkuJ0n/45yQAMg0Y6F817Hwnc+bx0kVpEYylZTSB64k8/9+Xg5kFrbQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Amount in local currency" = _t, #"Start Fiscal Month" = _t, #"Start Fiscal Year" = _t, #"End Fiscal Month" = _t, #"End Fiscal Year" = _t, #"Amortization Period in months" = _t, #"Per Period Amortization Amount" = _t]),
chgTypes = Table.TransformColumnTypes(Source,{{"Amount in local currency", type number}, {"Start Fiscal Month", Int64.Type}, {"Start Fiscal Year", Int64.Type}, {"End Fiscal Month", Int64.Type}, {"End Fiscal Year", Int64.Type}, {"Amortization Period in months", Int64.Type}, {"Per Period Amortization Amount", type number}}),
addRowNumber = Table.AddColumn(chgTypes, "rowNumber", each {0..[Amortization Period in months] - 1}),
expandRowNumber = Table.ExpandListColumn(addRowNumber, "rowNumber"),
addPeriod =
Table.AddColumn(
expandRowNumber,
"period",
each let
year = Text.From([Start Fiscal Year] + (Number.RoundUp(([Start Fiscal Month] + [rowNumber]) / 12) - 1)),
month = Text.PadStart(Text.From(Number.Mod([Start Fiscal Month] + [rowNumber] - 1, 12) + 1), 2, "0")
in
Number.From(Text.Combine({year, month}))),
remOthCols = Table.SelectColumns(addPeriod,{"Unique ID", "Start Fiscal Month", "End Fiscal Month", "Per Period Amortization Amount", "period"})
in
remOthCols
To get this:
Pete
Proud to be a Datanaut!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
65 | |
61 | |
21 | |
18 | |
12 |