Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm trying to find a way to accurately display revenue through two lenses:
The first lens is displaying revenue by the current region that a certain transaction was carried out in, where all transactions from a certain site are aligned to a current region in a static master table.
The second lens is displaying revenue by the historical region that the transaction lived in throughout it's lifetime, based on movement of that particular site from one region to another. The historical regions are captured in a duplicate master table that I want to dynamically update the values of, based on a time filter, grabbing the values from a change log table.
Please see dummy data/tables below:
Master Site Table (shows the CURRENT region value)
Site | Region |
1 | A |
2 | A |
3 | B |
4 | C |
Revenue Table (10 of 000s of rows of random transactions)
Transaction Date | Site | Value |
27/12/2017 | 4 | 290 |
16/09/2017 | 1 | 411 |
17/05/2017 | 2 | 509 |
27/11/2017 | 2 | 538 |
19/05/2017 | 2 | 653 |
28/12/2017 | 1 | 481 |
16/03/2017 | 3 | 326 |
05/02/2017 | 4 | 687 |
18/04/2017 | 2 | 113 |
Change Log Table (shows the HISTORICAL region value change over time)
Site | Category | Value | Effective From Date |
1 | Region | B | 09/01/2017 |
2 | Region | B | 01/02/2017 |
1 | Region | A | 31/03/2017 |
2 | Region | A | 26/04/2017 |
Desired output:
The desired output would be to dynamically create a 4th table, a replica of the master site table, where the region values dynamically update based on a time filter. If the user selects a date back in time, the table should look up the site and region in the change log table, analysing the effective from date, in order to see if the region value should be updated to it's historical value.
Any help or guidance on this would be very much appreciated!
Thanks,
Aaron
Solved! Go to Solution.
Hi @Anonymous
My understanding is, that you need a way to tell the historic region?
Assuming that my understading was right, you should be able to tell your current region and Historic region at the same time as demonsreated below.
You could also use the Transaction Date to see when the change happend.
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
I think you need a slow changing dimension, I've converted Change Log Table to Historic Master Site Table and added SiteId to both Historic Master Site Table and Revenue Table so you can create a relatioship.
Please see below M code example.
Master Site Table - you can disable load on this table as filds are merged to Hisoric Table
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUXJUitWJVjKEs4yBLCcwywTIclaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Site = _t, Region = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Site", Int64.Type}, {"Region", type text}}) in #"Changed Type"
Historic Master Site Table
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlHSUQpKTc/MzwMynIHYwFTfwEjfyMDQXClWJ1rJGFneCYgNzfQNjBHyhhjyBvqGhgh5Iyz6gUqw63cEYmNDFPON0OUNLfQNTKDysQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Site = _t, Category = _t, Value = _t, #"Effective From Date" = _t]), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Site", Int64.Type}, {"Category", type text}, {"Value", type text}, {"Effective From Date", type date}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "SiteId", 0, 1), #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"SiteId", Int64.Type}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"SiteId", "Value", "Effective From Date", "Site"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Value", "Historic Region"}}), #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Site"}, #"Master Site Table", {"Site"}, "Master Site Table", JoinKind.LeftOuter), #"Expanded Master Site Table" = Table.ExpandTableColumn(#"Merged Queries", "Master Site Table", {"Region"}, {"Region"}) in #"Expanded Master Site Table"
Revenue Table
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5LDsAgCAXvwtoEHv7wLKb3v0ZRY6NdvNVkGHonrQxlFVQKlHzahJ7QCYWlbYABgQUqS95AfVnaBOMULhBtGe1nlByXYUd8NgxfPG4Qx7RM4HfkerdYXYaxpLMBeON5AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Transaction Date" = _t, Site = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction Date", type date}, {"Site", Int64.Type}, {"Value", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "SiteId", each let sid = [Site], td = [Transaction Date] in Table.FirstN( Table.Sort( Table.SelectRows( #"Historic Master Site Table", each [Site] = sid and [Effective From Date] <= td ), {{"Effective From Date", Order.Descending}} ), 1 )[SiteId]), #"Expanded SiteId" = Table.ExpandListColumn(#"Added Custom", "SiteId"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded SiteId",{{"SiteId", Int64.Type}}) in #"Changed Type1"
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @Mariusz,
Thanks for the response. I really appreciate the help!
I updated my tables using the code you provided but I'm still struggling to understand how exactly it works and how this solves my problem. Would you mind expanding your explanation a little?
Cheers,
Aaron
Hi @Anonymous
My understanding is, that you need a way to tell the historic region?
Assuming that my understading was right, you should be able to tell your current region and Historic region at the same time as demonsreated below.
You could also use the Transaction Date to see when the change happend.
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |