Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm seeking help in writing an expression that will limit the results based on the max value date in two separate columns. The first column is a Fiscal Year (2014, 2013, 2012...) and the second column contains a Date (10/31/2014, 11/30/2014, 12/31/2014).
What I currently have returns results for the max Fiscal Year:
Sum({$<"Fiscal Year"={$(=max("Fiscal Year"))}>}[Adjusted Fair Value])
I'd like to add in the max Date to this expression, but not clear on the syntax. Any help would be appreciated.
That will get you values for exactly the Max(Date) ... If you want up to and including Max(Date) it has to be
like this:
Sum( { $< Date={ "<=Num(Max(Date))" } > } [Adjusted Fair Value] )
remember to count your brackets/ellipsis/paranthesis to get them matched or the expression will fail...
Something like this?
Sum({$<[Fiscal Year]={$(=max("Fiscal Year"))},[Date]={$(=Num(Max(Date)))}>} [Adjusted Fair Value])
Or do you want to use the max of one of the two columns?
So I have a column that contains a Fiscal Year and a column with a Date (Quarter End Date). I want to see the data for a specific year and a specific Date (Quarter End). I tried the expression above and it did not work. What could I be doing wrong?
The Date alone is sufficient if it is a full date stored - with the year...
Ok, so would the expression look like this:
Sum({$<"Date"={$(=max(Date)))}>} [Adjusted Fair Value])
That will get you values for exactly the Max(Date) ... If you want up to and including Max(Date) it has to be
like this:
Sum( { $< Date={ "<=Num(Max(Date))" } > } [Adjusted Fair Value] )
remember to count your brackets/ellipsis/paranthesis to get them matched or the expression will fail...
Petter,
Thanks for your help on this. I need to revisit how my data is structured and will comeback to you on this one. I don't think I need two date columns when one will suffice.