Forum Discussion
Calculating 2 fields based on filters
Hi All,
I am trying to add (sum) 2 values that belongs to 2 different years (based on a filter). I am trying to add remaining budget to the new year budget. I added the below table for illustration i need to calcualte field x.
| Budget | Remaining | Field x | |
| 2015/2016 | 1,000 | 1,000 | |
| 2016/2017 | 2,000 | 500 | Remaining (2015/2016) + Budget (2016/2017) |
| 2017/2018 | 1,000 | 100 | Remaining (2016/2017) + Budget (2017/2018) |
| 2018/2019 | 400 | 0 | Remaining (2017/2018) + Budget (2018/2019) |
Thank you in-advance for your help.
barazima ,
To achieve your requirement, create a calculate column using DAX below:
Field x = VAR Current_Index = 'Table'[Index] VAR Remaining_Previous_Year = CALCULATE(MAX('Table'[Remaining]), FILTER('Table', 'Table'[Index] = Current_Index - 1)) VAR Budget_Current_Year = CALCULATE(MAX('Table'[Budget]), FILTER('Table', 'Table'[Index] = Current_Index)) RETURN Remaining_Previous_Year + Budget_Current_YearCommunity Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
barazima ,
To achieve your requirement, create a calculate column using DAX below:
Field x = VAR Current_Index = 'Table'[Index] VAR Remaining_Previous_Year = CALCULATE(MAX('Table'[Remaining]), FILTER('Table', 'Table'[Index] = Current_Index - 1)) VAR Budget_Current_Year = CALCULATE(MAX('Table'[Budget]), FILTER('Table', 'Table'[Index] = Current_Index)) RETURN Remaining_Previous_Year + Budget_Current_YearCommunity Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yuta-msft Thank you for your great support. The DAX formula worked great. however, i had to change the MAX to SUM as i did not create an Index column but created a new column that converted the year (i.e. 2017/2018 = 2017) to make the formula work.
Thank you!
