6 Replies Latest reply on Jan 13, 2017 1:29 PM by Jamieson Christian

# Show MAX for a measure based on date not included in view

I never know if my titles make sense, but in the attached workbook I need to calculate the Max(BudgetYTD) for all of the months included in the "YTD CY" filter.  In the case for November that value would be (3,279,315.00) for Account7Dig 6080.10.  Really, I just need to bring in the BudgetYTD for the Max(DateTime) gathered form the filter.

I've been messing with LOD and index calcs forever, but I'm not getting anywhere.  Thanks for any help.

• ###### 1. Re: Show MAX for a measure based on date not included in view

If [YTD CY] will always be present as a context filter:

Otherwise:

Note that I used MIN() because your numbers are all negative. If you have positive numbers, you would use MAX().

Attached is a workbook showing the output for the latter.

• ###### 2. Re: Show MAX for a measure based on date not included in view

Oh. My. Lord. This is embarrassing...Switched to a min and all is well.  Thank you sir!

• ###### 3. Re: Show MAX for a measure based on date not included in view

Hi Mark,

I think you're right about needing LODs.  I generally use FIXED LODs, as they seem easier to me    Try something like this:

{FIXED [account]:

MAX([budgetYTD])

}

I would be more specific, but I can't open your file due to versioning issues :/

• ###### 4. Re: Show MAX for a measure based on date not included in view

Hi Mark

Combined LOD is needed with creating Year/Month Filed for easier interpretation.

[Max Bdget YTD]

{fixed [Account7Dig]:

max(if [DateTime (Month / Year)]={fixed:max([DateTime (Month / Year)])} then [BudgetYTD] end)}

Thanks,

Shin

• ###### 5. Re: Show MAX for a measure based on date not included in view

So far, all I've needed to do was switch my calculation - actually instead I changed BudgetYTD to be a positive, because that's how it needs to be displayed anyway and the MAX calculation by itself is working fine without LODs...

Thank you everyone for all the helpful info.  It's amazing how much time I spent trying different calculations when it turned out to be a simple switch from positive to negative.  Autopilot on full force today.

• ###### 6. Re: Show MAX for a measure based on date not included in view

Mark,