Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to subtract the values between two months in the same column using calculated column

Hi Experts,

 

please help me to acheive the below:

 

I tried hard but nothing is working.

 

I want to subtract the value  = Dec-Jan = -16.28

 

Please help me on this.

 

Regards.,

Swamy

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    Below is my table:

    You can create a index column:

    The following DAX might work for you:

    output = 
var _a =  CALCULATE(max('table'[value]),filter('table','table'[index]=earlier('table'[index])+1))
RETURN
  'Table'[Value] - _a

    The final output is shown in the following figure:

    Best Regards,

    Xianda Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Thank you for Syndicate_Admin answer , and I have other suggestions:

    Below is my table:

    The following DAX might work for you:

    Column = 
 VAR _month = MIN('Table'[Year])
 VAR _new_month = MONTH(EDATE(_month,1))
 VAR _a = CALCULATE(MAX('Table'[FIFO]),FILTER('Table',MONTH('Table'[Year]) = _new_month))
 VAR _b = CALCULATE(MAX('Table'[FIFO]),FILTER('Table',MONTH('Table'[Year]) = MONTH(_month)))
 RETURN
 _b - _a

    The final output is shown in the following figure:

    Best Regards,

    Xianda Tang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Syndicate_Admin 

       

      thanks for your dax, but I need to subtract the value from current month to previous month as below:

       

       

      Regards.,

      Swamy

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous ,

        Below is my table:

        You can create a index column:

        The following DAX might work for you:

        output = 
var _a =  CALCULATE(max('table'[value]),filter('table','table'[index]=earlier('table'[index])+1))
RETURN
  'Table'[Value] - _a

        The final output is shown in the following figure:

        Best Regards,

        Xianda Tang

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ray_Minds's avatar
    Ray_Minds
    Icon for Solution Supplier rankSolution Supplier
    2 years ago

    You could be use measure instead of calculated column. I believe measure is best option to summarize the expression and also good to performance. you will use below expression as seen in the snapshot.