SUM current and Previous records

[madan27]

Hi,

I am having a table as below

Year	Designation	Salary
2010	Engineer	50000
2010	Sr.Engineer	70000
2010	A.Manager	90000
2011	Engineer	55000
2011	Sr.Engineer	75000
2011	A.Manager	95000
2012	Engineer	65000
2012	Sr.Engineer	85000
2012	A.Manager	105000

In the table I am able to filter the records based on Designation (using Visual Level Filter option in Power BI)

For example I am filtered the designation as 'Enginner'

Year	Designation	Salary
2010	Engineer	50000
2011	Engineer	55000
2012	Engineer	65000

Now I need to derive the result as below

Year	Designation	Salary	Sum
2010	Engineer	50000	5000
2011	Engineer	55000	105000
2012	Engineer	65000	170000

I need to derive the SUM by adding the Current and Previous years salary. Kindly help me to get the above result table.

Thanks,

Corrected years.

[v-ljerr-msft]

Hi,

@Anonymous, I agree with @Vvelarde that madan27 needs to calculate the SUM by adding the Current and all Previous years' salary(not just the last previous year). 

@madan27, the formula provided @Vvelarde above should work in your scenario. Just replace "Table1" with your real table name, and use the formula to create a new measure, then show it on the Table visual with other corresponding columns.

Regards

[Vvelarde · Accepted solution]

@madan27

Hi, try witt this DAX

Salary running total in Year =
CALCULATE (
    SUM ( 'Table1'[Salary] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Designation] ),
        Table1[Year] <= MAX ( Table1[Year] )
    )
)

[madan27]

Thanks @Vvelarde. I got the required ouput.  

[Anonymous]

@Vvelarde That is going to give a running total of ALL years.  I read the requirement as current year, plus 1 year ago?  I could be nuts, but if not...

Maybe something like... ?

Salary CY :=
CALCULATE (
    SUM ( Table1[Salary] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Designation] ),
        Table1[Year] = MAX ( Table1[Year] )
    )
)

Salary PY := 
CALCULATE (
    SUM ( Table1[Salary] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Designation] ),
        Table1[Year] = MAX ( Table1[Year] ) - 1
    )
)

Salary CY+PY := [Salary CY] + [Salary PY]

[Phil_Seamark]

Hi @madan27

In the expected output you have two Engineer records for 2011.  Should one of these be for 2012?

[madan27]

Yes Phil, your correct. Sorry for the typo....