Using RANKX to get a records Value on a Particular Date
I am trying to create a measure that generates daily data showing what Status an ID is in, when the source data itself does not exist in a daily matter. Ultimately, I want to show how many records were in each state on each day of a specified time period as well as a table showing the status for each day.
To accomplish this, I am trying to use the RANKX function to determine what is the most recent state to the day being evaluated. I am having trouble getting the syntax to give the results I am looking for. I have attached some source sample data as well as the Expected results.
Below is one attempt I've made, where _date is coming from a seperate Date Table that has no relationship to my Table. I am able to get the proper Table (via the Filter expression in the first argument) to be used in RANKX, its the 2nd Expression argument that I am having trouble with.
var _date = MAX(Calendar[Date])
var _id = MAX(Table[ID])
Table[ChangedDate']<=_date && Table[ID]=_id),