Need help with a dynamic table that updates values based on time filter

[Anonymous]

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

[Mariusz]

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.

[Anonymous]

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

[Mariusz · Accepted 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.