6 Replies Latest reply on Jan 12, 2017 11:26 PM by Norbert Maijoor

    Compare +/- % against previous 30 days

    mike.shapiro.0

      Hi, I'm building a dashboard that reports a 30 day average in large font.  I'm looking to include a +/- percentage calculation against how this number compares to the previous 30 days.  Similar to how you see stock quotes.

       

      Let's just say the measure is AVG[measure] as an example.  What's the optimal way to write this as a custom measure?

        • 1. Re: Compare +/- % against previous 30 days
          Norbert Maijoor

          Hi Mike,

           

          Find my approach below and stored in attached workbook version 9.3

           

           

          a. Datediff:      DATEDIFF('day',[Date],#29-4-2014#,'sunday')

           

          b. Amount 30 days: if [Datediff]<=30 then [Amount] END

           

          c. Amount 31-60 days: if [Datediff]>30 and [Datediff]<=60  then [Amount] END

           

          d. First 30 days / second 30 days: sum([amount 30 days])/sum([amount 31-60 days])

          1 of 1 people found this helpful
          • 2. Re: Compare +/- % against previous 30 days
            mike.shapiro.0

            Hi Norbert,

             

            I'm on a new project that is looking at the +/-, but is a bit more tricky.   This time, we are using a relative date filter within the dashboard that allows user to select a date range of their choice.  What I'm looking for is a way to look at the +/- from an equal previous period.


            Examples:

            - 30 days select, then +/- from 31-60 days ago

            - 6 months date range, then +/- from the previous 6 months data

             

            Let me know if you have any experience with this. Thanks!

            • 3. Re: Compare +/- % against previous 30 days
              Norbert Maijoor

              Morning Mike,

               

              Find my approach as reference below and stored in attached workbook version 9.3

               

              • 4. Re: Compare +/- % against previous 30 days
                mike.shapiro.0

                Thank you sir. 

                 

                I'm working this through, but coming across a couple challenges. 

                 

                Both the date filter I'm using and the measure are custom measures.  [Switch Date Calc] looks at two date dimensions and decides which one to use.  [M3T Product Switch] is already aggregated and looks at multiple measures to decide which one to use.

                 

                When I call that, I get a can't mix aggregate, non aggregate error.  I threw in a ATTR in front of [Switch Date Cal], but no results are populating. It does show it as valid though.   Also, I merged it all into one custom measure as shown below.

                 

                [Date Filter] is the parameter.

                 

                "

                /// Returns value of prior period  looking @ date filter parameter.

                IF [Date Filter] = 1 THEN

                IF datediff('day',ATTR([Switch Date Calc]),today())>=31 and datediff('day',ATTR([Switch Date Calc]),today())<=60 then [M3T Product Switch]

                ELSEIF [Date Filter] = 2 THEN

                if datediff('month',ATTR([Switch Date Calc]),today())>=1 and datediff('month',ATTR([Switch Date Calc]),today())<=3 then [M3T Product Switch]

                ELSEIF [Date Filter] = 3 THEN

                if datediff('month',ATTR([Switch Date Calc]),today())>=1 and datediff('month',ATTR([Switch Date Calc]),today())<=6 then [M3T Product Switch]
                END END END END

                "

                • 5. Re: Compare +/- % against previous 30 days
                  mike.shapiro.0

                  [M3T Product Switch] is an aggregated measure.  Having issue getting this code to work.

                  • 6. Re: Compare +/- % against previous 30 days
                    Norbert Maijoor

                    Goodmorning Mike,

                     

                    Could you share your workbook. That will be the most practical way to help you out.

                    Hope to hear from you.

                    Have a nice one.