Forum Discussion
Visualizing Historical Data
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.
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.
12 Replies
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.
Attributes Version 0 Version 1 Start Date 01/01/26 02/01/26 End Date 01/31/28 01/31/28 Current Price $101.00 $97.00 Current Price Valid From 01/01/26 02/01/26 I'm thinking of a multi-row card for the agreement number and contracting entity.
Hi,
Sorry but from this information, i cannotunderstand the result that you want to generate.
Hi tom-lenzmeier ,
I tried to reproduce the scenario using a small sample dataset and was able to achieve the expected result.
For better understanding, I’m sharing a screenshot of the sample matrix visual that shows the attributes as rows and the agreement versions as columns.
FYI:
Please take a look and let me know if this aligns with what you are trying to achieve, or if any additional clarification is needed.
Thank you.
Yes! That's it. Can you make a suggestion about how you versioned the records? Also, what properties did you set for the matrix? I'm familiar with a type 2 slowly changing dimension using this method: https://learn.microsoft.com/en-us/fabric/data-factory/slowly-changing-dimension-type-two
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.
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.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.
Hi tom-lenzmeier ,
As Ashish_Mathur mentioned earlier, an example dataset would help demonstrate the versioned matrix approach more clearly. If you could share a few sample rows from your table and what the expected result should look like, it will be easier for the community to suggest the best way to structure the matrix and filtering logic.
Regards,
Yugandhar.
