Visualizing Historical Data

[tom-lenzmeier]

Greetings!

I'm in need of suggestions regarding how to best visualize a type 2 slowly changing dimension in PowerBI. The dimension I am working with is a sales agreement table. The table has the type 2 values of StartDate, EndDate, & IsCurrent. The attributes I'm tracking are list price, current price, and price valid from. In a visual I'd like to show the history of a single agreement with the current and expired records as columns. Rows would be the agreement number and the attributes. I would also like to only show those agreements that have changed. A bit puzzled as to how best to accomplish this. Thanks in advance.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Zanqueta]

Hi @tom-lenzmeier. In my opinnion for visualising a Slowly Changing Dimension Type 2 (SCD2) in Power BI, particularly for a sales agreement table where you wish to display historical versions side‑by‑side, what may work well is to restructure the dimension into a format that supports a clear “versioned matrix” layout. This allows you to display each historical record (expired and current) as separate columns, while listing attributes as rows. You can also apply a filter that returns only agreements with at least one historical change. 

Use a Matrix visual with:

• Rows: AgreementID → AttributeName
• Columns: VersionNumber
• Values: AttributeValue

If you prefer to show only two columns—Current Record and Historical Record—you can create logic to identify the current version (IsCurrent = 1) and the prior version(s) (IsCurrent = 0). The principle remains the same, but the matrix will contain two fixed columns rather than multiple version columns.

[tom-lenzmeier]

@Zanqueta I appreciate your response. Do you have an example of a versioned matrix you'd be able to share? I'm trying to think about how to restructure the table so I can work with your versioned idea.