Adding custom column that adds most recent data for rows in historical dimension table

[ChrisLoan]

Hi everyone!

I have a question. We have a dimension table for customers which is set up so it keeps track of history and changes made to the customer data.

For example, if someone from our company changes the address of a company, our stored procedures in our SQL server will add the company as a new row, with the new address, a valid from to range (set to the date when they changed the address) and a "current record flag".

This is useful to dynamically change our Fact Invoice data according to history. But now I've run into an issue.

The customers have a "credit limit", so just like all the other fields they change according to de valid from to range date based on the year or date you select in our Power BI apps. However I would like to create a column that fills in the most recent "credit limit" value for all the historical rows.

Here's a visual to explain it a bit:

How do I add this column (highlighted in orange) in Power BI:

Thanks!

[talespin · Accepted solution]

Hi @ChrisLoan ,

Assuming that most recent credit limit has a valid to date of "9999-12-31" and there will always be only one record for most recent credit limit, another way to write it. This is a calculated column.

"DimCustomer[Customername] = _customername" you can do without it as during context transition, filters are automatically propagated, but I like to do it manually to help my brain convince that context transition is easy 😄

//Since we are in a row context, fetching current customer

VAR _customername = DimCustomer[Customername]

//for the customer in current row fetching the value of credit limit beased on below filters. Here context transition is happening.

return CALCULATE(VALUES(DimCustomer[CreditLimit]), ALLEXCEPT(DimCustomer, DimCustomer[Customername]), DimCustomer[Customername] = _customername , FORMAT(DimCustomer[ValidTo], "YYYY-MM-DD") = "9999-12-31")

[ChrisLoan]

First tests are looking very promising! Thank you so much!

I'm gonna run a few more tests today and tomorrow and if everything works I'll accept this answer as a solution 😊

[ChrisLoan]

Thanks! I'll give this a try in a couple of hours and see if it works 😊

[123abc]

To achieve this in Power BI, you can create a new column using DAX (Data Analysis Expressions) to fetch the most recent "credit limit" value for each historical row in your customer dimension table. You can use the following steps:

1. Sort the Customer Dimension Table: Ensure that your customer dimension table is sorted by the "Valid From" column in descending order so that the most recent record appears first for each customer.

2. Create a New Column: Create a new column in Power BI using the following DAX formula. This formula uses the CALCULATE function in combination with FILTER to find the most recent "credit limit" value for each customer based on the sorted order.

MostRecentCreditLimit =
CALCULATE(
MAX('YourTable'[CreditLimit]),
FILTER(
'YourTable',
'YourTable'[CustomerID] = EARLIER('YourTable'[CustomerID]) &&
'YourTable'[ValidFrom] <= EARLIER('YourTable'[ValidFrom])
)
)

1. Replace 'YourTable' with the actual name of your customer dimension table, and 'CustomerID', 'CreditLimit', and 'ValidFrom' with the corresponding column names in your table.

2. Use the New Column in Visualizations: Once you've added the new column, you can use it in your Power BI visualizations to show the most recent "credit limit" value for each historical row.

Remember to adjust the column names based on your actual table structure. This DAX formula should provide you with the most recent "credit limit" value for each historical row in your customer dimension table.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[ChrisLoan]

I tried this but I still get the same values per row as before, not the most recent ones for all of them .. 

Thanks anyway, I'll try a few more times maybe I'm using the wrong sorting in Power BI?

[Anonymous]

HI @ChrisLoan,

Current power bi does not support to create dynamic calculated column/table based on filter effect. They not work on the same level and you can't use child level to affect their parent.

Notice: the data level of power bi(from parent to child level)

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng