Working with YearName in expressions

[StuartJohnson]

Hi All,

I'm trying to populate a pivot table with sales data for this and last financial years. Currently i can do that for this year using the following expression: =sum({<[FYear] = {"=YearName(Today())"}>}[Units]).

however if i try and do the same for last year using the expression: =sum({<[FYear] = {"=YearName(addyears(Today(),-1))"}>}[Units]), i get the same data for both columns.

Can someone explain where/why i'm going wrong please.

[pradosh_thakur]

can you try this

=sum({<[FYear] = {"$(=YearName(addyears(Today(),-1)))"}>}[Units])

[StuartJohnson]

Thanks i tried that, but it doesn't show any sales when i use that expression

[sunny_talwar]

How do you create FYear in the script? Is it created using Year function? If it is, then try this

=Sum({<[FYear] = {"$(=Year(AddYears(Today(), -1)))"}>} [Units])

[StuartJohnson]

I created FYear using the below;

Yearname(TempDate2, 0, $(vFiscalYearStartMonth)) as FYear it creates a valur in the format 2018-2019. If i use the string it returns 0 for sales, if i use your formula it still returns 0 sales. But i can get it to work for this year?

[sunny_talwar]

How about this then

=Sum({<[FYear] = {"$(=YearName(Today(), 0, $(vFiscalYearStartMonth)))"}>} [Units])

[StuartJohnson]

My variables are stored in my load tabs, can they still be used on sheets. However this still returns 0?

[sunny_talwar]

Can you check what do you get when you use this in a text box object

=YearName(Today(), 0, $(vFiscalYearStartMonth))

[sunny_talwar]

Also, check when you use this

=YearName(AddYears(Today(), -1), 0, $(vFiscalYearStartMonth))

[StuartJohnson]

I get 2018-2019