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.

[StuartJohnson]

For the second expression i get the correct 2017-2018. i guess i must be doing something wrong elsewhere.

[sunny_talwar]

Now can you check when you use this

=Sum({<[FYear] = {"2018-2019"}>} [Units])

and

=Sum({<[FYear] = {"2017-2018"}>} [Units])

[sunny_talwar]

If these two worked... I am almost certain that these should work as well...

Current Year

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

Last Year

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

[StuartJohnson]

the first one 2018-2109 returns 0, the second 2017-2018 returns a value

[sunny_talwar]

Do you have data for 2018-2019? Also, make sure that there is not another selection which contradicts your set analysis?

[StuartJohnson]

Correction, they both return values.

[StuartJohnson]

Using your latest expressions i get sales for this year and -1 for all rows in Last year

[sunny_talwar · Accepted solution]

Awesome, then try these

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

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

[StuartJohnson]

Thank you, so much. now they're both returning values. I dont suppose you could explain why what i was trying initially wouldn't work?

[sunny_talwar]

First, you were not using the dollar sign expansion in your variable... which does a row by row comparison... to compare FYear to a single value... you need a dollar sign expansion

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

Second, I think you probably will get different values if you use this is a text box

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

vs this

 =YearName(AddYears(Today(), -1))

 Which is why I believe @pradosh_thakur's expression didn't work for you... otherwise his expression would have worked.