Select Value by Reference Date

[emmet]

I have a dataset with four fields:

1. Customer ID

2. Old Status

3. New Status

4. Status Change Date

Note that the Customer ID field is non-unique. In other words, over the timeframe of the data, the same customer can have multiple status changes.

There are six unique Status values. I want to show a breakdown of status by date (for example, a 100% stacked bar chart showing all statuses as of 1/1/2018). This means that the total count of Statuses being reported should equal the distinct count of Customer IDs.

I was able to solve this by creating a reference table summarizing Customer IDs and pulling in the correct date using the following logic:

-If the minimum date a Customer ID has in the data is greater than the reference date, I want to report the Old Status associated with that minimum date and Customer ID. Otherwise, I want to report the New Status associated with the maximum date that is less than the reference date.

From there, using a Join Key ([Customer ID] & [Status Change Date]) I was able to pull in the correct status value from the original table. However, the performance of this is very slow, and I am hoping to improve it. Any thoughts on how to improve performance?

Any help you might have is appreciated.

[Greg_Deckler]

Sample data please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[emmet]

Hi @Greg_Deckler, thank you for your response. I've created sample data that you can use here.