cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
aaron92 Regular Visitor
Regular Visitor

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

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)

SiteRegion
1A
2A
3B
4C

 

Revenue Table (10 of 000s of rows of random transactions)

Transaction DateSiteValue
27/12/20174290
16/09/20171411
17/05/20172509
27/11/20172538
19/05/20172653
28/12/20171481
16/03/20173326
05/02/20174687
18/04/20172113

 

Change Log Table (shows the HISTORICAL region value change over time)

SiteCategoryValueEffective From Date
1RegionB09/01/2017
2RegionB

01/02/2017

1RegionA

31/03/2017

2RegionA

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

Hi @aaron92 

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.

 

image.png

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.

 

3 REPLIES 3
Super User
Super User

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

Hi @aaron92 

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.

aaron92 Regular Visitor
Regular Visitor

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

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

Super User
Super User

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

Hi @aaron92 

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.

 

image.png

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.

 

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 306 members 3,408 guests
Please welcome our newest community members: