Forum Discussion
Solved
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 |
Thank you in advance,
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
1 Reply
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