How to get sum of the amount based on previous category value?

[Anonymous]

I have a table with following columns: fiscal year and the amount (See the table below).

TABLE1

Fiscal_Year	Amount
2002	$100
2002	$200
2003	$75
2003	$95
2003	$37
2004	$30
2004	$90
2005	$45
2006	$99

Also, I have a fiscal year table. This table has One to Many relationship with TABLE1.

FY_Table

Fiscal_Year

2002

2003

2004

2005

2006

I want to calculate the previous year's amount.

Fiscal_Year	Amount	LY_Amount
2002	$300
2003	$207	$300
2004	$120	$207
2005	$45	$120
2006	$99	$45

Following is my trial code which seems to give me all blank in the LY_Amount:

measure--> LY_Amount =

var LY_Fiscal_Year = SELECTEDVALUE(FY_Table[Fiscal_Year])-1

return (calculate(sum(TABLE1[Amount]), FILTER(TABLE1, TABLE1[Fiscal_Year] = LY_Fiscal_Year)))

[amitchandak]

@Anonymous , Try like

var LY_Fiscal_Year = SELECTEDVALUE(FY_Table[Fiscal_Year])-1

return (calculate(sum(TABLE1[Amount]), FILTER(all(FY_Table), FY_Table[Fiscal_Year]= LY_Fiscal_Year)))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[Anonymous]

Nope, Nothing. The result is coming all blank

Fiscal_Year	Amount	LY_Amount
2002	$300	BLANK
2003	$207	BLANK
2004	$120	BLANK
2005	$45	BLANK
2006	$99	BLANK

[Anonymous · Accepted solution]

Thanks for all your help. I used the following code.

var LY_Fiscal_Year = SELECTEDVALUE(TABLE1[Fiscal_Year])-1

return (calculate(sum(TABLE1[Amount]), all(TABLE1), TABLE1[Fiscal_Year]= LY_Fiscal_Year)))