Subtract 2nd row with first row in table view

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
)

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

 

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

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