Measure difference value between years - how to calculate?

[Anonymous]

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!

[v-ljerr-msft]

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.

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]

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]

Hi @Anonymous,

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

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

Regards

[Anonymous]

That formula's syntax works, but it's not giving the correct value.  

Since the 'value' measure is really another measure divided by a different value, I could be making it more confusing.  What you provided seems to be adding data and not sure from where.

Let me play around with what you've given me so far & see if I can make it work.  Thanks!

[v-ljerr-msft]

Hi @Anonymous,

Have you solved this issue? If you still have any question on it, feel free to post it here. 

If the issue got resolved, could post the solution here to help others who may also have the similar issue easily find the answer and close this thread? 

Regards