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

# Compare +/- % against previous 30 days

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

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

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

Morning Mike,

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

• ###### 4. Re: Compare +/- % against previous 30 days

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

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

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

Goodmorning Mike,