Hello everybody!
I have two columns for Fiscal Year (FY) and Fiscal Month (FM) and I'm trying to create a calculation that will return the max(FM) where FY = max(FY). I tried the calc below but it's don't work :{
IF([Max Date] = Max(FY) THEN MAX(FM) ELSE NULL
where Max Date is a calculation that returns the max(FY).
Thanks,
Brian
hi Brian,
So an LoD is probably the easiest way to go here. Try this
[Last FM]
{FIXED: MAX(
iif([FY] = {FIXED: MAX([FY])}, [FM], null)
)}
With an LoD this will now return this to every row in the data, so you can do things like
[YtD Sales]
iif([FM] <= [Last FM], [Sales],null)
Bring this in, and put Year in the level of detail and it will only display the FM = 1 to Current FM for each year (or just use the [Last FM] as a filter...lots of option.
Hope that does the trick, but let me know if not (I've not used the Table Calculation Editor, so may have missed a bracket here or there!)
