Forum Discussion

aatish178's avatar
aatish178
Icon for Helper IV rankHelper IV
2 years ago
Solved

Subtracting row wise value from same column and showing the result as difference in new column

Hi Team,

I have a table consisting of Fiscal year FY20 and FY21(both FYs starts from July) and for that I have count of employee ID column next to it...

Now I want to subtract count of empid for FY20 from FY21 without hardcoding the FYs in DAX. In future if FY22 comes then the subtraction should happen from FY21 to FY22..And so on

 

I have used EARLIER function but no luck yet. 

 

Please help me if something can be done in such case

 

 

subtraction of Row
  • quantumudit's avatar
    quantumudit
    2 years ago

    Hi aatish178 

     

    You may use the following DAX formula to create a calculated column in the table to dynamically get the difference in employee count from the previous year

     

    Difference = 
// Get Current and Previous Year
VAR _currentFY = CONVERT ( RIGHT ( DiffTable[FY], 2 ), INTEGER )
VAR _previousFY = _currentFY - 1 

// Get Employee ID count for current year
VAR _empCurrentFY =
    CALCULATE (
        SUM ( DiffTable[Count of EmpID] ),
        FILTER ( DiffTable, DiffTable[FY] = "FY" & _currentFY )
    ) 
    
// Get Employee ID count for previous year
VAR _empPreviousFY =
    CALCULATE (
        SUM ( DiffTable[Count of EmpID] ),
        FILTER ( DiffTable, DiffTable[FY] = "FY" & _previousFY )
    )
RETURN
    IF(
        ISBLANK(_empPreviousFY), 0,
        ABS ( _empPreviousFY - _empCurrentFY )
    ) // Absolute difference in employee count

     

    Here is the screenshot of the solution

     

     

     

    I hope this will help but, let me know if anything.

     

    Best Regards,
    Udit

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudo 👍

    🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
    ✨ Visit My Linktree: LinkTree

21 Replies

    • aatish178's avatar
      aatish178
      Icon for Helper IV rankHelper IV
      2 years ago

      Hi, 

      Yes

      ------------------------------------

      FY | Count of EmpID | Difference

      -------------------------------------

      FY20 | 100 | 0 (which is 100-100)

      FY21 | 200 | -100 (100 - 200)

      FY22 | 50 |  150 (200-50)

       

      Hope this dataset helps

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Dear 
    i need to find the difference between two value of different months, in my data e.g ID is common for ALL month, and against each ID have Months,  i need to calculate the difference between( Mar.23 - Apr.23) against the respective ID.