Forum Discussion
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.
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)
Hi Anonymous ,
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 _ranknew 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.
1 Reply
Hi Anonymous ,
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 _ranknew 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.