excluding new employees from salary averages

[fcastle]

Hi, been a long time since posting here, but here goes;

I have three tables:

Transactions:

Employee:

and Date (standard date table), with these relationships:

I have one measure:

Salary increase per year =
VAR thisyear =
    CALCULATE ( SUM ( transactions[Amount] ) )
VAR lastyear =
    CALCULATE ( SUM ( transactions[Amount] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR rawcalc = ( thisyear - lastyear ) / lastyear
RETURN
    IFERROR ( rawcalc, BLANK () )

I think I need to filter "this" years totals based on the individuals having a sum in the previous year, but every variant i try seems to be a dead end.

Any insights are much appreciated!

[fcastle · Accepted solution]

Thanks for your assistrance @Greg_Deckler 

I sorted it out when reading this.
https://p3adaptive.com/2015/08/nested-sumx-or-dax-query/

[Greg_Deckler]

@fcastle Try:

Salary increase per year =
VAR thisyear =
    CALCULATE ( SUM ( transactions[Amount] ) )
VAR lastyear =
    CALCULATE ( SUM ( transactions[Amount] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR rawcalc = ( thisyear - lastyear ) / lastyear
RETURN
    IF(ISBLANK(lastyear),BLANK(),IFERROR ( rawcalc, BLANK () ))

[fcastle]

@Greg_Deckler 

Thanks but issue persists. I think the key is to find a way to pass a list of dimensions (employeeid) to a sum measure.

[Greg_Deckler]

@fcastle Perhaps:

Salary increase per year =
VAR __Table = SUMMARIZE('transactions',[employeeid],"increase",
VAR thisyear =
    CALCULATE ( SUM ( transactions[Amount] ) )
VAR lastyear =
    CALCULATE ( SUM ( transactions[Amount] ), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR rawcalc = ( thisyear - lastyear ) / lastyear
RETURN
    IF(ISBLANK(lastyear),BLANK(),IFERROR ( rawcalc, BLANK () )))
RETURN
  SUMX(__Table,[increase])

[fcastle]

@Greg_Deckler 

no cigar unfortunately 🙂

[Greg_Deckler]

@fcastle Paste the sample data as text or provide a link to the sample PBIX table so I can recreate.

[fcastle]

@Greg_Deckler 

first; thank you very much for helping out a mate, greatly appreciated!

link to sample file:
https://1drv.ms/u/s!AtjU9aGZ2mSSgRqKvKE8ZC7ZLQZB?e=XTnfo9