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'm not sure if anyone is able to assist to combine two tables based on different date ranges or having a lookup in dax to add values to an existing table based on date ranges. This has to do with foreign currency translation. Where different rates are applicable for different date ranges.
Example
Two Tables
From Currency | To Currency | Effective Date | Multiplier | GL Date | Base Currency | Amount | ||
GBP | AUD | 31/12/2023 | 0.564 | 2/01/2023 | GBP | 45,826.00 | ||
GBP | AUD | 31/03/2023 | 0.5443 | 4/04/2023 | GBP | 23,468.00 | ||
GBP | AUD | 31/05/2023 | 0.5237 | 2/03/2023 | CAD | 12,456.00 | ||
GBP | AUD | 30/06/2023 | 0.5318 | 30/06/2023 | CAD | 3,256.00 | ||
CAD | AUD | 31/12/2023 | 0.8825 | |||||
CAD | AUD | 31/03/2023 | 0.8887 | |||||
CAD | AUD | 31/04/2023 | 0.9067 | |||||
CAD | AUD | 30/05/2023 | 0.9173 | |||||
After | ||||||||
GL Date | Base Currency | Amount | Multiplier | |||||
2/01/2023 | GBP | 45,826.00 | 0.564 | |||||
4/04/2023 | GBP | 23,468.00 | 0.5443 | |||||
2/03/2023 | CAD | 12,456.00 | 0.8825 | |||||
30/06/2023 | CAD | 3,256.00 | 0.9173 |
Solved! Go to Solution.
@MdJ83 make sure you use correct dates (you have 31st of April in your data). Also make sure you have dates in rates table earlier than min date in GL table. Replace your_rates_table and your_GL_table in code below with correct references to your tables.
let
// this is your table with rates. Had to trim ccy column later.
s_rates = your_rates_table,
date_type = Table.TransformColumnTypes(s_rates, {{"Effective Date", type date}}, "en-AU"),
trimmed = Table.TransformColumns(date_type,{{"From Currency", Text.Trim, type text}}),
// this is your table with amounts
before = your_GL_table,
b_type = Table.TransformColumnTypes(before, {{"GL Date", type date}}, "en-AU"),
// make a record with ccy name fields and list of dates and rates sorted by date
rates =
Table.Group(
trimmed, "From Currency",
{{"rates", each List.Sort( List.Zip({_[Effective Date], _[Multiplier]}), {(x) => x{0}, Order.Descending})}}
),
rec = Record.FromList(rates[rates], rates[From Currency]),
// add Multiplier column.
after =
Table.AddColumn(
b_type, "Multiplier",
each [a = Record.FieldOrDefault(rec, [Base Currency], null),
b = a{List.PositionOf(a, [GL Date], Occurrence.First, (x, y) => x{0} <= y)},
c = if b = -1 then null else b{1}][c]
)
in
after
This works well but does cause having a duplicate table with the same transactional information and cannot delete the orginal table.
Is there a way to combine the s_rates into the table with the amounts so that before read the table with the amounts
@MdJ83 make sure you use correct dates (you have 31st of April in your data). Also make sure you have dates in rates table earlier than min date in GL table. Replace your_rates_table and your_GL_table in code below with correct references to your tables.
let
// this is your table with rates. Had to trim ccy column later.
s_rates = your_rates_table,
date_type = Table.TransformColumnTypes(s_rates, {{"Effective Date", type date}}, "en-AU"),
trimmed = Table.TransformColumns(date_type,{{"From Currency", Text.Trim, type text}}),
// this is your table with amounts
before = your_GL_table,
b_type = Table.TransformColumnTypes(before, {{"GL Date", type date}}, "en-AU"),
// make a record with ccy name fields and list of dates and rates sorted by date
rates =
Table.Group(
trimmed, "From Currency",
{{"rates", each List.Sort( List.Zip({_[Effective Date], _[Multiplier]}), {(x) => x{0}, Order.Descending})}}
),
rec = Record.FromList(rates[rates], rates[From Currency]),
// add Multiplier column.
after =
Table.AddColumn(
b_type, "Multiplier",
each [a = Record.FieldOrDefault(rec, [Base Currency], null),
b = a{List.PositionOf(a, [GL Date], Occurrence.First, (x, y) => x{0} <= y)},
c = if b = -1 then null else b{1}][c]
)
in
after
Is there a way to not make a new table but to include the mutliplier into table with the amounts. I presume one would need to change the before to the current table but not sure if that is creating a cross reference.
This code works, but does include quite a few steps and merging queries. I am sure someone in here will post a more elegant solution. My solution requires you to load both tables into Power Query and go from there.
First table with the multiplier data in which you can change my source step to your source step:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"From Currency", type text}, {"To Currency", type text}, {"Effective Date", type date}, {"Multiplier", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
#"Sorted Rows" = Table.Sort(#"Added Index1",{{"Index", Order.Ascending}}),
#"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"From Currency", "To Currency", "Index.1"}, #"Sorted Rows", {"From Currency", "To Currency", "Index"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Effective Date"}, {"Effective Date.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "EndDate", each Number.From([Effective Date.1])-1),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"From Currency", Text.Trim, type text}})
in
#"Trimmed Text"
second table in which you also have to change the source accordlingly:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"GL Date", type date}, {"Base Currency", type text}, {"Amount", type number}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Base Currency"}, DateRanges, {"From Currency"}, "DateRanges", JoinKind.LeftOuter),
#"Expanded DateRanges" = Table.ExpandTableColumn(#"Merged Queries", "DateRanges", {"Effective Date", "Multiplier", "Effective Date.1"}, {"Effective Date", "Multiplier", "Effective Date.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded DateRanges", "MultiplierV2", each if [Effective Date.1] = null and [GL Date] >= [Effective Date] then "keep" else if [GL Date] >= [Effective Date] and [GL Date] <=[Effective Date.1] then "keep" else
"delete"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([MultiplierV2] = "keep")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"GL Date", "Base Currency", "Amount", "Multiplier"})
in
#"Removed Other Columns"
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 |
---|---|
67 | |
61 | |
23 | |
17 | |
12 |