Subtracting from the same column based on select filters Options

0
votes

I was wondering if someone can help me with a DAX formula. I currently have data similar to this:

DATA

I have a year range from 2007 - 2018. I would like to create a DAX formula that would calculate the difference of the last column when I select 2 years based on the years selected in a filter. E.g. if i have selected 2009 and 2011 Vietnam would be 119,859 - 271,400 = -151,541 or if it was Vietnam 2009 and 2017 432,490 - 271,400 = 161,090

1
powerbidax

1 Answers

1
votes

You'll need to create a new table to use for your year slicer. You should be able to simply write a calculated table like this:

Years = VALUES(Countries[Year])

(assuming your main data table is named Countries and has a Year column).

Once you have that, you just need to define a measure that takes the difference.

Diff = 
    VAR MaxYear = MAX(Years[Year])
    VAR MinYear = Min(Years[Year])
    RETURN CALCULATE(SUM(Countries[Value]), Countries[Year] = MaxYear) -
           CALCULATE(SUM(Countries[Value]), Countries[Year] = MinYear)

You should be able to set up a slicer and matrix now like this:

Slicer and Matrix