Forum Discussion
How to get Previous Year Aggregate Values
I need to take Aggregated Salary values for Previous year where Year Column is not Date column -
| Employee_ID | Year | Month | Salary |
| 123 | 1 | 1 | 300 |
| 123 | 1 | 2 | 100 |
| 123 | 1 | 3 | 200 |
| 123 | 2 | 6 | 250 |
| 123 | 2 | 8 | 250 |
| 123 | 2 | 9 | 250 |
Output Expected -
| Employee_ID | Year | Salary | Previous Year Salary |
| 123 | 1 | 600 | |
| 123 | 2 | 750 | 600 |
5 Replies
powerbi_2022
This thread may help you !https://blog.crossjoin.co.uk/2010/05/26/time-intelligence-without-date-columns-in-dax/You could try it like this.
PY Salary = VAR _Year = SELECTEDVALUE ('Table'[Year] ) RETURN CALCULATE ( SUM ('Table'[Salary] ),'Table'[Year] = _Year - 1 )Not returning any value.
It returns for me based on your sample data.
Hi powerbi_2022 ,
In this visual, is the [Year] column in the same table as the [Salary] column (i.e. 'Table')? If it is in the same table then the formula of jdbuchanan71 will return the correct value, if it is not in the same table then replace "Table" with the table where the [Year] column is located.
For example in my model, I need to use 'Date'[YearNum].
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
