Forum Discussion
Sandeep13
Helper III
Helper IIISubtract 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...
bhanu_gautam
Super User
Super UserSandeep13 Select your table Sort_Client_MIS.
Create a new column by clicking on "New Column" in the "Modeling" tab.
Use the following DAX formula to create the Visit_cnt column:
DAX
Visit_cnt =
VAR CurrentRow = 'Sort_Client_MIS'[ECV_VISIT]
VAR PreviousRow =
CALCULATE(
MAX('Sort_Client_MIS'[ECV_VISIT]),
FILTER(
'Sort_Client_MIS',
'Sort_Client_MIS'[HIT_FROM] = EARLIER('Sort_Client_MIS'[HIT_FROM]) &&
'Sort_Client_MIS'[ECV_VISIT] < EARLIER('Sort_Client_MIS'[ECV_VISIT])
)
)
RETURN
IF(
ISBLANK(PreviousRow),
CurrentRow,
CurrentRow - PreviousRow
)
Sandeep13Thsnks for your reply
I am using your script for sample data but still getting wrong output
Not HIT_From is sorted in asc order
Date HIT_FROM ECV_VISIT Visit 3/1/2025 ABCD_EM 1770 1770 3/2/2025 ABCD_EM 3629 1859 3/3/2025 ABCD_EM 5702 2073 3/4/2025 ABCD_EM 7761 2059 3/5/2025 ABCD_EM 10675 2914 3/6/2025 ABCD_EM 13961 3286 3/7/2025 ABCD_EM 17071 3110 3/8/2025 ABCD_EM 20927 3856 3/9/2025 ABCD_EM 23869 2942 3/10/2025 ABCD_EM 27256 3387 3/11/2025 ABCD_EM 29790 2534 3/12/2025 ABCD_EM 33001 3211 3/13/2025 ABCD_EM 34914 1913 3/14/2025 ABCD_EM 36921 2007 3/15/2025 ABCD_EM 39045 2124 3/16/2025 ABCD_EM 39045 0 3/1/2025 ADMINFM 5136 5136 3/2/2025 ADMINFM 7597 2461 excel formula