Forum Discussion
Solved
find value at calculated date
I need to find a value based on a calculated date - ie what was [NewValue] on the first instance it was one of "Put it Right", "Stage 1" , "Stage 2" or "Stage 3". This is the measure I'm using to fin...
Please try
FirstStageDate = VAR CurrentIDTable = CALCULATETABLE ( 'History: Complaint', ALLEXCEPT ( 'History: Complaint', 'History: Complaint'[ParentId] ) ) VAR FilteredTable = FILTER ( CurrentIDTable, 'History: Complaint'[NewValue] = "Put it Right" || 'History: Complaint'[NewValue] = "Stage 1" || 'History: Complaint'[NewValue] = "Stage 2" || 'History: Complaint'[NewValue] = "Stage 3" ) VAR MinDate = MINX ( FilteredTable, 'History: Complaint'[CreatedDate] ) RETURN MAXX ( FILTER ( FilteredTable, 'History: Complaint'[CreatedDate] = MinDate ), 'History: Complaint'[Value] )
You can try
Value at earliest stage =
VAR summaryTable =
TOPN (
1,
FILTER (
ALLEXCEPT ( 'History: Complaint', 'History: Complaint'[ParentId] ),
TREATAS (
{ "Put it Right", "Stage 1", "Stage 2", "Stage 3" },
'History: Complaint'[NewValue]
)
),
'History: Complaint'[CreatedDate]
)
RETURN
SELECTCOLUMNS ( summaryTable, [NewValue] )