Forum Discussion
Visualizing Historical Data
Hi tom-lenzmeier ,
In my example, I used the IsCurrent flag in the table to determine the version. I added a column where IsCurrent = 1 is labeled as Version 1 (current record), and IsCurrent = 0 as Version 0 (historical record).
I also transformed the data in Power Query by unpivoting columns like StartDate, EndDate, CurrentPrice, and PriceValidFrom, so these attributes are displayed as rows.
For the matrix visual, I set:
Rows: Attributes
Columns: Version
Values: AttributeValue
I disabled row and column subtotals so only the values appear side by side in the matrix.
Since your table already uses the SCD Type 2 structure (StartDate, EndDate, IsCurrent) as described in the documentation, this approach should work for you as well.
If you need more details, just let me know.
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.
- Rows: AgreementID → AttributeName
- Columns: VersionNumber
- Values: AttributeValue
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.
