cancel
Showing results for
Did you mean:
Highlighted
Super User I

## Dynamic calculation based on filter selection (DAX not M)

Hello experts,

My sample data is as following

 BU Year GL Amount 1 2016 511200 100 1 2017 511200 200 1 2018 511200 300 1 2019 511200 400

All I want is the DAX to calculate based on a filter selection on year the last 2 year's corresponding value.
e.g. if the reader selects 2019 from the filter, I want the following view to be returned in the matrix viz

 BU GL Selected Year Amount Selected Year-1 Amount Selected Year-2 Amount 1 511200 400 300 200

e.g. if the reader selects 2018 from the filter, I want the following tview to be returned in the matrix viz

 BU GL Selected Year Amount Selected Year-1 Amount Selected Year-2 Amount 1 511200 300 200 100

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I

## Re: Dynamic calculation based on filter selection (DAX not M)

Figured out as below

```Selected Year-1 Total =
VAR f=FILTERS('Table 1'[Year])
VAR f_1=f-1
VAR x=CALCULATE(SUM('Table 1'[Amount]),FILTER(ALL('Table 1'),'Table 1'[Year]=f_1))
RETURN
x```
Highlighted
Super User I

## Re: Dynamic calculation based on filter selection (DAX not M)

Figured out as below

```Selected Year-1 Total =
VAR f=FILTERS('Table 1'[Year])
VAR f_1=f-1
VAR x=CALCULATE(SUM('Table 1'[Amount]),FILTER(ALL('Table 1'),'Table 1'[Year]=f_1))
RETURN
x```

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors