Subtract 2nd row with first row in table view

[Sandeep13]

 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 sample data for your reference and my output expection. 

Note: this is in example with formula I am using in excel

    =IF([@[HIT_FROM]]=B1|C2-C1|[@[ECV_VISIT]])

you can see I am subtrating ECV_VISIT column value 2nd row with 1st row from second row.
my first row will be 1770 but second row will 3629-1770 =1859 and so on 

HIT_FROM	ECV_VISIT	Visit expected output
ABCD_EM	1770	1770
ABCD_EM	3629	1859    how 1859 comes (3629-1170)
ABCD_EM	5702	2073
ABCD_EM	7761	2059
ABCD_EM	10675	2914
ABCD_EM	13961	3286
ABCD_EM	17071	3110
ABCD_EM	20927	3856
ABCD_EM	23869	2942
ABCD_EM	27256	3387
ABCD_EM	29790	2534
ABCD_EM	33001	3211
ABCD_EM	34914	1913
ABCD_EM	36921	2007
ABCD_EM	39045	2124
ABCD_EM	39045	0 this is bcoz of same  vlues subtraction
ADMINFM	5136	5136  here as new hit form ADMINFM comes so it start with 5136
ADMINFM	7597	2461    (7597-5136 =2461)
ADMINFM	13905	6308

@Expert @microsa @BI 

[AntrikshSharma]

@Sandeep13 First make sure you have an Index column that enforces uniqueness of rows otherwise rows might appear in different order than the source and DAX functions will only rely on = and >< operations.

Load the Table and create the calculated column with the following DAX:

Result = 
VAR PreviousVisit = 
    SELECTCOLUMNS ( 
        OFFSET ( 
            -1, 
            ALL ( Sort_Client_MIS ), 
            ORDERBY ( Sort_Client_MIS[Index], ASC ),
            PARTITIONBY ( Sort_Client_MIS[HIT_FROM] ),
            MATCHBY ( Sort_Client_MIS[Index] )
        ),
        Sort_Client_MIS[ECV_VISIT]
    )
VAR CurrentVisit = Sort_Client_MIS[ECV_VISIT]
VAR Result = 
    CurrentVisit - PreviousVisit
RETURN
    Result

[v-vpabbu]

Hi @Sandeep13,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @Sandeep13,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @Sandeep13,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[danextian]

Hi @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

[Sandeep13]

Hi @danextian 

One correction: If you see ABCD_EM 39045, it is showing difference 2124, but ideally, it should show 0. This is because the subtraction should be 39045 (15th Mar) - 39045 (16th Mar), which equals 0.

[bhanu_gautam]

@Sandeep13 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
)

[Sandeep13]

Thsnks 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