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...
bhanu_gautam
Super User
1 year agoSandeep13 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
Helper III
1 year agoThsnks 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