Forum Discussion
Sandeep13
Helper III
1 year agoSubtract 2nd row with first row in table view
Hi Team I want to subtract 2nd row with first row in table view. I have table name Sort_Client_MIS with 2 columns HIT_FROM and ECV_VISIT and want to create new column Visit_cnt Below is samp...
danextian
Super User
1 year agoHi Sandeep13
Try this:
Difference =
VAR _hit_from = 'datatable'[HIT_FROM] -- Stores the current row's HIT_FROM value
VAR _ecv_visit = 'datatable'[ECV_VISIT] -- Stores the current row's ECV_VISIT value
VAR _prevvalue =
MAXX (
FILTER (
ALL ( 'datatable' ),
-- Ignores existing filters to scan the entire table
'datatable'[HIT_FROM] = _hit_from -- Keeps only rows with the same HIT_FROM
&& 'datatable'[ECV_VISIT] < _ecv_visit -- Finds rows with an earlier ECV_VISIT
),
[ECV_VISIT] -- Retrieves the maximum (latest) previous ECV_VISIT
)
RETURN
'datatable'[ECV_VISIT] - _prevvalue
-- Calculates the difference from the previous ECV_VISIT