Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Measure difference value between years - how to calculate?

I'm looking to use my date/year slicer to show the difference in values based on the following:

 

Item1 2014 $100

Item1 2015 $105 $5

Item1 2016 $110 $10

 

I'd like to have a new column at the end in my Matrix to show the increase/difference from 2014 to 2016, or whatever item/year I have selected from the slicer.  Can this be done easily, or is there more context reuquired for what I'm after?

 

Thank you!

Need Help

5 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

     

    I assume you have a table called "Table1" like below.

     

     

    Then you should be able to use the formula below to create a measure to calculate the increase/difference, then show the measure on the report.:smileyhappy:

    Difference =
VAR minYearSelected =
    CALCULATE ( MIN ( Table1[Year] ), ALLSELECTED ( Table1 ) )
RETURN
    SUMX (
        Table1,
        Table1[Value]
            - CALCULATE (
                SUM ( Table1[Value] ),
                FILTER ( ALL ( Table1 ), Table1[Year] = minYearSelected )
            )
    )

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      If it matters, the field "value" is a measure from a SUM of other data.  So, this is what I have and Power BI says "Too many arguments were passed to the SUM function. The maximum argument count for the function is 1"

       

      Difference = VAR minYearSelected = CALCULATE(min ('Table1'[Year]), ALLSELECTED( 'Table1' ) ) RETURN SUMX ('Table1',[Value] - CALCULATE (SUM (([Value]), FILTER( ALL('Table1'),'Table1'[Year] = minYearSelected ) ) ))

       

      Is the measure throwing it off or am I missing something else?

       

      Thank you!

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi Anonymous,

         

        Please try the formula below to see if it works in your scenario.:smileyhappy:

        Difference =
VAR minYearSelected =
    CALCULATE ( MIN ( 'Table1'[Year] ), ALLSELECTED ( 'Table1' ) )
RETURN
    SUMX (
        'Table1',
        [Value]
            - CALCULATE (
                [Value],
                FILTER ( ALL ( 'Table1' ), 'Table1'[Year] = minYearSelected )
            )
    )

         

        Regards