Stuck on basic issue -- earlier function

[charleshale]

Hi.  This is a really dumb question.  I should be demoted in my powerbi community levels.  Let's say I have a table like this -- intentionally not connected to any date table (so I can't use PREVIOUSYEAR(date[date] or the like).

YEAR  -- ID -- FTE

2019  -- 23 -- .75

2019  -- 24 -- .6

2020  -- 23 -- 1.1

2020  -- 24 -- 3

Let's say I wanted to add a column that shows simply the prior year's value of FTE such that the table would look like this:

YEAR  -- ID -- FTE -- PRIOR YEAR FTE

2019  -- 23 -- .75 -- null

2019  -- 24 -- .6 -- null

2020  -- 23 -- 1.1 --.75

2020  -- 24 -- 3 -- .6

What would be the best way to do this?   Rember - I dont have this connected to a date table intentionally (multiple 100m row data table so I'm trying to keep the joins to as few as possible unless critical.) 

This may be too much information but if it helps, 

(1) the current FTE calc comes from a CALCULATE(sumx(DISTINCT(BigTable[ID]),sum(BigTable[FTE]))

(2) so I tried a simple:

Calculate (

sumx(DISTINCT(BigTable[ID]),sum(BigTable[FTE])),

FILTER (

'BigTable,

'BigTable'[Year] = EARLIER ( [Year] )

But I must be blowing the filter context because I fail to pull the right value

[Jihwan_Kim · Accepted solution]

Prior Year FTE =
MAXX (
FILTER (
'Table',
'Table'[Year]
= EARLIER ( 'Table'[Year] ) - 1
&& 'Table'[ID] = EARLIER ( 'Table'[ID] )
),
'Table'[FTE]
)

[smpa01 · Accepted solution]

@charleshale  I am not sure why you wrote FTE like CALCULATE(sumx(DISTINCT(BigTable[ID]),sum(BigTable[FTE]))? You can probably simplify this as follows. When you are dealing with a 100mil dataset, each and every measure you write needs to be performant else you will run into serious issues.

_sum = SUM(BigTable[FTE])

once you have it, you can get previous year some for the same id like following

_prevSum =
VAR _year =
    MAX ( BigTable[Year] ) - 1
RETURN
    CALCULATE ( [_sum], BigTable[Year] = _year )

[charleshale]

Well shoot good catch on the overcomplication of the sumx . 

The _prevsum works beautifull Thank you.   

Correction:  it's now failing to give me the previous year after having worked.   Maybe I've a got bug somewhere.  This is very strange

[Jihwan_Kim · Accepted solution]

Prior Year FTE =
MAXX (
FILTER (
'Table',
'Table'[Year]
= EARLIER ( 'Table'[Year] ) - 1
&& 'Table'[ID] = EARLIER ( 'Table'[ID] )
),
'Table'[FTE]
)

[charleshale]

Thanks, @Jihwan_Kim .  When I try this configuration, I am unable to escape the row context of the date, such that I get the current year instead of the previous year.  

[amitchandak]

@charleshale , FTE  is a column and you want a new column

new column =

sumx(filter(table, Table[Year] = earlier(Table[Year]) -1 ), [FTE])

If you need a measure

new meausre=

sumx(filter(all(table), Table[Year] = Max(Table[Year]) -1 ), [FTE])

or

new measure=

sumx(filter(allselected(table), Table[Year] = max(Table[Year]) -1 ), [FTE])

[charleshale]

The great and ubiquitous @amitchandak !   Thank you for your response.  It must be the oddity of my particular model but I get the current year value when I try  this.