The three tables I have are shown below
Customer Historty Table
|ID||CustomerID||PostedDate||Old Address||New Address|
|951753||879564213||1/10/2021||123 N Way||789 S Broadway|
|753951||879564213||1/01/2021||Null||123 N Way|
|357159||231564879||12/31/2021||Null||456 E Central|
|ID (CustomerID)||ReportedDate||Current Address|
|879564213||1/10/2021||789 S Broadway|
|231564879||12/31/2021||456 E Central|
Currently if I wanted to pull order information for OrderID 987564213 it would return multiple address or just the current one. If I only tied it to the Customer Table then I would only get the most current address, not necessarily the one the customer lived at when they made the order. But if I tie it to the Customer Historty Table then I would get multiple address for the order with no way of knowing which one was the ACTUAL address at the time of the order.
What I would like is either a new column in the Customer Historty Table that would expand the table to include every single day and put the respective customer address for each day. Or a mesure in the Order Table that could do something similar, by using the order date to determine which Address to use if its after or before they changed address.
What I have tried is a mesure that checks the order date and compares it to the latest PostedDate in the customer history table. This did not work as some customers have moved many times a few years so using the latest date wouldnt work.
I am not sure if I understood correctly but in essence: you want to get the NewAddress information for a order during the time of order (SurveyDate)?
This can be achieved with the following measure:
@ValtteriN Thanks for the response, Im having trouble understadning how this would work? So if im inderstanding the DAX correctly it is making a variable for the date of the order. Then returing the value in the new_address coloumn if posted date is less than or equal to the variable orderdate. How does that return the correct address if 9/01/2021 (orderDate) is greather than the last posted date? Also what does the MAX function do here? Thanks
Hi, The MAX is used on the variable to select a single value. Without MAX of something similar like selectedvalue The formula doesn't work. The Logic behind The posted date is as follows: The way I understood your question is that 'customer history' contains the date when a customers address changed [posted date]. If this posted date is lower than the order date DAX selects The MAX case our of this data so the latest row where the customer's address changed. Does this clarify the DAX?
Yes postedDate is when a customers address was changed. I just asking because I am trying to understand why its not working. I am looking at one customer and all address returned ,total of 5, are the same and its returning the most recent address not the one at the time of the order. Would that fact the relationship is many to many break the DAX?
Yeah, like you suggested m:n relationship will cause issues. This is the data model I used in my example:
Is it possible to modify your model to mirror this? (I used CustomerID as the key)
I changed the model to look like that, and now its returning the same address for all rows of that customer, and wierdest part its not even an address thats associated with that customer.
That sounds quite confusing. My first instinct is that there is a problem with relationships or that the column you are using in the visulization causes the issue. Typically when a measure returns the same value for all the rows there is a issue with the relationship this is especially true if the row is duplicated or the returned value doesn't make any sense. In this case since the measure I used in my example returns values that make sense I am leaning even more towards this.
Usually in this situation I double the data intregity of my key columns. So my question at this point is are you sure the data type is the same with your ID columns and have you done "clean" and "trim" steps in powerquery?
@Anonymous, You can add a new column in order and can connect directly with Customer History Table
Join ID =
var _max = maxx(filter('Customer Historty Table','Customer Historty Table'[PostedDate] <='Order'[SurveyDate]
&& 'Customer Historty Table'[CustomerID] <='Order'[CustomerID] ),'Customer Historty Table'[PostedDate])
maxx(filter('Customer Historty Table','Customer Historty Table'[PostedDate] =_max
&& 'Customer Historty Table'[CustomerID] <='Order'[CustomerID] ),'Customer Historty Table'[ID])
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
We had a great 2022 with a ton of feature releases to help you drive a data culture.