cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Trying to Create new Column/measure thats matched on ID and a Date Range

The three tables I have are shown below

Customer Historty Table

IDCustomerIDPostedDateOld AddressNew Address
9517538795642131/10/2021123 N Way789 S Broadway
7539518795642131/01/2021Null123 N Way
35715923156487912/31/2021Null456 E Central

Customer Table 

ID (CustomerID)ReportedDateCurrent Address
8795642131/10/2021789 S Broadway
23156487912/31/2021456 E Central

 

Order Table

OrderIDCustomerIDSurveyDate
 9875642138795642131/12/2021
 3216549878795642131/12/2021

 

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. 

8 REPLIES 8
ValtteriN
Super User
Super User

Hi NotRick,

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: 

Addres during the time of order =
var orderDate = MAX(OrderTable[SurveyDate]) return
CALCULATE(MAX(CustomerHistory[New Address]),CustomerHistory[PostedDate]<=orderDate)

While using this measure in the order table the end results looks as follows: 
ValtteriN_0-1638619827599.png

 


Hopefully this solves your problem.
 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@ValtteriN
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: 

ValtteriN_0-1638817355468.png

Is it possible to modify your model to mirror this? (I used CustomerID as the key)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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. 

Hi,

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? 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

@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])
return
maxx(filter('Customer Historty Table','Customer Historty Table'[PostedDate] =_max
&& 'Customer Historty Table'[CustomerID] <='Order'[CustomerID] ),'Customer Historty Table'[ID])

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors