5 Replies Latest reply on Sep 23, 2016 5:49 AM by sam.xia.2

    Maximum date based on parameter date

    brian.chu

      If my data looks like:

       

      1/1/2016, 10

      2/1/2016, 20

      3/1/2016, 30

      4/1/2016, 40

       

      And I have a parameter set at 3/1/2016,

       

      How do I return the value 20?

       

      That is, how do I return the measure value of the most recent date that is less than the parameter date?

       

      Sorry I don't have a workbook or better example. Hope it makes sense. Thanks.

        • 1. Re: Maximum date based on parameter date
          David Li

          Hi Brian! There are a few ways to do this, depending on your needs.

           

          The easiest way to do this is to drag your date dimension onto the filters shelf and pick "Individual dates".

          Then, go into the Condition tab and write a formula along these lines:

          Then, go into the Top tab and choose Top 1 by date, max.

          Of course, you'll also need to put whatever measure you want to display into the view before or after these steps.

          • 2. Re: Maximum date based on parameter date
            sam.xia.2

            Hey Brian,

             

            Bit of a complex solution, but gives you a bit more functionality.

             

            You could also create a calculated field using level of detail which will create a field where each row of the field would contain the most recent value associated with the most recent date.

             

            So you can do calculations using that value as well.

             

            The example is below:

             

            For data ->

             

            Create a calulated field with the below formula (The calculated field can be used for calculations where ever you need):

             

            { FIXED : MAX(IIF({ FIXED : MAX(IIF([Date]<=[Parameter],[Date],NULL))}=[Date],[Value],NULL))}

             

            • 3. Re: Maximum date based on parameter date
              brian.chu

              Thank you David. That does work, however, I think I might have simplified my example too much for my actual purposes.

               

              I can't really add Date to the filters because I require the whole date range even those after the parameter. Please see my reply to Sam below.

              • 4. Re: Maximum date based on parameter date
                brian.chu

                Thank you Sam, this seems to get me pretty close. I definitely made my example too simple though for my actual purposes.

                 

                My trouble is I have 4 dimensions before Date and I need to get the value of the 'max date before parameter date', restarting for each combination of those dimensions.

                 

                Essentially like a table calculation, but I know LOD expressions don't convert well to table calculations. I've tried changing up your LOD calculation but can't quite get it right. For example, those extra 4 dimensions also have filters so I've been using INCLUDE instead of FIXED.

                 

                Any further ideas are much appreciated.

                • 5. Re: Maximum date based on parameter date
                  sam.xia.2

                  Hey Brian,

                   

                  If you try and make those extra 4 dimension filters as Contextual Filters that could solve your problem.

                   

                  Let me know how it goes : ).