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.
Awesome, then try these
=Sum({<[FYear] = {"$(=YearName(Today(), 0, $(vFiscalYearStartMonth)))"}>} [Units])
=Sum({<[FYear] = {"$(=YearName(AddYears(Today(), -1), 0, $(vFiscalYearStartMonth)))"}>} [Units])
Thanks i tried that, but it doesn't show any sales when i use that expression
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])
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?
How about this then
=Sum({<[FYear] = {"$(=YearName(Today(), 0, $(vFiscalYearStartMonth)))"}>} [Units])
My variables are stored in my load tabs, can they still be used on sheets. However this still returns 0?
Can you check what do you get when you use this in a text box object
=YearName(Today(), 0, $(vFiscalYearStartMonth))
Also, check when you use this
=YearName(AddYears(Today(), -1), 0, $(vFiscalYearStartMonth))
I get 2018-2019