4 Replies Latest reply on Oct 13, 2016 8:21 AM by David Li

    Return Value for Min & Max Date in a Quarter


      I'm sure I'm missing something simple but can't get this to work.


      Sample data attached.


      Each week, we take a snapshot of what a value was on a specific date.  I want to display what the value was at the start of the quarter and at the end of the quarter (using Min & Max dates in a quarter). However, I can only get it to return the sum of the values and can't seem to extract the specific value for that min and max date. Have tried LOD calcs and can't get anything to work.


      Any ideas? The ultimate aim is to calculate the difference between the starting value and the final value in the qtr.


      I'd even accept just returning the value on the start of each quarter.  Any assistance appreciated.

      Thank you :-)

        • 1. Re: Return Value for Min & Max Date in a Quarter
          David Li

          Hi Dorothy! Is this what you're looking for?

          I started by creating a calc that separated the quarters:

          YEAR([Archive Date]) * 10 + DATEPART('quarter', [Archive Date])

          Then, I used two LOD calc to get the start and ending values. Here's the one for the ending value:

          { FIXED [Quarter] : SUM(IIF([Archive Date] = { FIXED [Quarter] : MAX([Archive Date]) }, [Revenue], NULL)) }

          Here, note that you can nest LOD calcs within LOD calcs.

          2 of 2 people found this helpful
          • 2. Re: Return Value for Min & Max Date in a Quarter
            Simon Runc

            hi Dorothy,


            Here you go! (I think anyway!)


            So first I created a Date which truncates to Quarter

            [Archive Date - Quater]

            DATETRUNC('quarter',[Archive Date])


            And then I can use this field, to identify the MIN and MAX day of each quarter

            [Min Date each Quater]

            {FIXED [Archive Date - Quater]: MIN([Archive Date])}


            [Max Date each Quater]

            {FIXED [Archive Date - Quater]: MAX([Archive Date])}


            Then we can just use this to bring out the revenue for these days

            [Start Quater Revenue]

            IF [Archive Date] = [Min Date each Quater] THEN [Revenue] END




            [End Quater Revenue]

            IF [Archive Date] = [Max Date each Quater] THEN [Revenue] END


            and then we can use these 2 to get our variance

            [Var Start/End Quarter]

            SUM([End Quater Revenue])/SUM([Start Quater Revenue])-1


            Although I've split these out into separate fields to make it easier to follow the steps, you can just nest these together into 1 or 2 calculated fields for your final version (but can keep separate too!!)


            Hope this helps, and makes sense...let me know if not

            1 of 1 people found this helpful
            • 3. Re: Return Value for Min & Max Date in a Quarter

              David Li / Simon Runc, thank you so much for your responses!!


              Interestingly, while both of them worked on the set of data I provided for this purpose, when I applied the two different methods to my real dataset, David, yours didn't work for it. It was summing up to numbers that I couldn't make sense of. It could be to do with how the number is calculated in our datawarehouse.  I'll play around with it to see if I can work it out (most likely to do with the summing) and will post back here if I get the answer on it!  One question I have that I'm sure is a simple explanation(!) is in your calc YEAR([Archive Date]) * 10 + DATEPART('quarter', [Archive Date])

              what does multiplying it by 10 mean? 


              Simon, your calculation has worked perfectly on the real dataset. I was missing step 1 altogether in all of this (plus a few other things...).


              Thank you both for your responses, I really appreciate it :-)

              • 4. Re: Return Value for Min & Max Date in a Quarter
                David Li

                You're welcome! Simon's way is better; I should have thought of using DATETRUNC instead of doing what I did, which was basically to create a numeric quarter using the year and the quarter number. Multiplying by 10 just moves the date up one place so that the quarter can fit in.


                The probable reason that it didn't work is that the LOD calculation uses just Quarter in its dimensionality argument, which means that it'll essentially ignore any breakdowns you try to do in the sheet. That is, it's giving you the total revenue for the start and end dates for each quarter for the full data set, even if you were to filter or aggregate by some other dimension. One way to fix this is by using EXCLUDE [Archive Date] as the dimensionality argument instead of using FIXED. The downside of this is that using EXCLUDE and INCLUDE won't give you true row-level values like FIXED will.


                I think I did it this way because of how your data is laid out in your sample workbook. There, you have each day in a separate row. I'm pretty sure that Simon's method won't give you any value for the rows that contain days that are not the start and end date; however, if you just have quarters in your level of detail (and don't show specific dates), Simon's way is better.