3 Replies Latest reply on Feb 15, 2018 1:33 AM by Michael Huynh

# Measures relative to dynamically selectable date periods

Hi,

I am trying to have measures [sales] change dynamically with order dates that a user can set by parameter [from date] & [to date].

I have managed to do this for the selected period, but I was wanting to do this using the previous period as well.

I've made a calculated field Sales Previous Period:

IF ATTR([Order Date]) >= DATEADD('day',-[Date Difference],[From Date])

AND ATTR([Order Date]) <= DATEADD('day',-[Date Difference], [To Date])

THEN SUM([Sales]) END

But this is not yielding results. I'm guessing it has something to do with my Date Difference calculation or using ATTR in the the above calculation for Order Date, to work around the aggregate and non-aggregate measure issue caused by using date difference.

If anyone has experience with this or could help explain why my calculation is not working  it would be greatly appreciated. My workbook is attached.

Many thanks!

Michael

• ###### 1. Re: Measures relative to dynamically selectable date periods

See attached.

• ###### 2. Re: Measures relative to dynamically selectable date periods

I can tell that you previously has the SUM()  outside the "IF" logic, and you were on the right track there.

The problem you had with aggregates and non-aggregates was in the date difference calc.  Don't need the ATTR in there.

Also I displayed the DATEDIFF result for the previous "to-date" value you are getting in the [Sales Previous Period] calc.  You really want to subtract ([Date Difference] + 1).  otherwise you'll be ending the previous range on the start of the current range.

• ###### 3. Re: Measures relative to dynamically selectable date periods

Thanks for answering Joe. Removing ATTR from my DATEDIFF calculation worked.

The reason I was using ATTR(DATEDIFF) was that it would show the actual date difference eg. 61, where DATEDIFF without ATTR produces a result of 279,000 for dates 5/1/2017 to 6/30/2017.