Using RANKX to get a records Value on a Particular Date

[EaglesFly123]

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.

Sample Source Data & Expected Results

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.

Rank =
var _date = MAX(Calendar[Date])
var _id = MAX(Table[ID])
Return 
    RANKX(
        FILTER(
            Table,
            Table[ChangedDate']<=_date && Table[ID]=_id),
        CALCULATE(
            SUM(ChangedDate)),,
        ASC)

[v-yangliu-msft · Accepted solution]

Hi  @EaglesFly123 ,

Here are the steps you can follow：

Show how many records there are in each state every day in the specified time period:

1. Create calculated table.

Table 2 =
SUMMARIZE('Table','Table'[Date],
"New",CALCULATE(COUNT('Table'[WorkItem ID]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[State]="New")),
"Reay",CALCULATE(COUNT('Table'[WorkItem ID]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[State]="Ready")),
"Active",CALCULATE(COUNT('Table'[WorkItem ID]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[State]="Active")),
"Test",CALCULATE(COUNT('Table'[WorkItem ID]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[State]="Test")),
"Done",CALCULATE(COUNT('Table'[WorkItem ID]),FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[State]="Done"))
)

2. Result:

Display the latest status every day:

Is your latest status based on the largest ID and grouping by date?

1. Create calculated column.

rank =
var _rank=RANKX(FILTER(ALL('Table'),'Table'[Date]=EARLIER('Table'[Date])),'Table'[WorkItem ID],,ASC)
return
_rank

new staus =
CALCULATE(MAX('Table'[State]),FILTER(ALL('Table'),'Table'[rank]=MAX('Table'[rank])&&'Table'[Date]=EARLIER('Table'[Date])) )

2. Result：

Best Regards,

Liu Yang

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