2 Replies Latest reply on Jun 1, 2016 10:35 AM by Brian Scelzo

    Level of Detail Formula Question - Assigning Current/Prior Based on Filtered Values

    Brian Scelzo

      Hi everyone - I've been wrestling with this issue for a bit now and hoping you friendly people can help!  I've attached a copy of a workbook with dummy data for reference.


      I have the following scenario:  we have a rolling forecast process and I want the users to be able to compare not only the most recent forecast version against a prior version, but also have the ability to compare two prior versions.  Currently, I'm using a LOD calc to assign a "Current" tag to the most recent forecast using an LOD calc as such:


      IF [Forecast Period Date] = { FIXED [Business Unit]: max([Forecast Period Date])} THEN "Current" ELSE "Prior" END


      This works great for comparing current vs. prior, but falls down when comparing prior vs. prior.  In the view in question, I use table calcs to find the difference between values, computed using the Current/Prior designation.  Ideally, I'm looking for this functionality:


      Working Formula using the Most Recent Version                                               This occurs when selecting two priors; what's needed is the 3x9 to read current vs. 2x10 to read prior.