Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have loaded in a table with Fiscal Year and RowNo() in order to be able to call the Fiscal Year value in set analysis through the max RowNo. I have done this because I was unable to call the maximum Fiscal Year (format - 'FY14').
My formula should sum Volume for the most recent FY in the model (i.e. max row number). My set analysis is currently not working:
=sum({$<[FiscalYear2]={$(=max(RowNo()))}>}[Volume])
Similarly, I would like to do the same for RowNo - 1, to return the 2nd most recent FY.
The script to create the table is as follows:
[FY Count]:
LOAD distinct
FiscalYear2,
RowNo()
resident [FYCount]
;
Please can you advise?
Thanks,
M
Hi M,
what does your model look like, you are using FiscalYear2 in your set analysis, but from the code this looks like it might be a data island and not connected to your Volume field?
Also you are using the date field which is in the format 'FYxx' but trying to select '3' within it, so the set won't work.
What you want to do I believe is to create a variable which based on your max rowno() returns the corresponding FiscalYear2 value, you can then use that variable in your sum expression.
Please see attached, hope that helps
Joe
for max FY use formula
=FirstSortedValue(FiscalYear2,-[RowNo()])
Also the original data seems to be different than the reflected one. Bz FY is a value derived from Dates not like this one.
Hi M,
what does your model look like, you are using FiscalYear2 in your set analysis, but from the code this looks like it might be a data island and not connected to your Volume field?
Also you are using the date field which is in the format 'FYxx' but trying to select '3' within it, so the set won't work.
What you want to do I believe is to create a variable which based on your max rowno() returns the corresponding FiscalYear2 value, you can then use that variable in your sum expression.
Please see attached, hope that helps
Joe
Hi Joe, yes that's exactly it!
I've amended my set analysis to call when RowNo() (which I've renamed to FYRow), which works:
=sum({$<FiscalYear2 = , [FYRow]={'2'}>}[Volume])
=sum({$<FiscalYear2 = , [FYRow]={'1'}>}[Volume])
Thanks for the help!
M
glad to help, sorry I thought you had those disconnected which is what I went with in my example.
One thing I would say is, you might want to avoid hardcoding the expressions like that, as it means next year you will need to adjust for the latest year, better to use a variable so it dynamically changes as time goes on
hope that helps
Joe