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 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors