Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
For the second expression i get the correct 2017-2018. i guess i must be doing something wrong elsewhere.
Now can you check when you use this
=Sum({<[FYear] = {"2018-2019"}>} [Units])
and
=Sum({<[FYear] = {"2017-2018"}>} [Units])
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])
the first one 2018-2109 returns 0, the second 2017-2018 returns a value
Do you have data for 2018-2019? Also, make sure that there is not another selection which contradicts your set analysis?
Correction, they both return values.
Using your latest expressions i get sales for this year and -1 for all rows in Last year
Awesome, then try these
=Sum({<[FYear] = {"$(=YearName(Today(), 0, $(vFiscalYearStartMonth)))"}>} [Units])
=Sum({<[FYear] = {"$(=YearName(AddYears(Today(), -1), 0, $(vFiscalYearStartMonth)))"}>} [Units])
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?
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.