How to retun a value from XX working days back in a calculated field

[nicoan]

Hi All

I´m trying to return the CLOSE value of a security from 21 trading days back (with symbol in rows context and a date in filter context).

The closest I got is this:
=CALCULATE(SUM(FactTable[CLOSE]),DATEADD(Calendar[FullDate],-30,DAY))

Not what we´re looking for, as it returns blank values for non-trading days, and the amount of trading days varies.

Looking at how to solve it, I found this great post from Alberto Ferrari:
Alberto Ferrari : Working days computation in PowerPivot

So I created a Calendar table and identified the trading days with a 1.
Now we´d just need to count them backwards from the date filter context and get the “CLOSE” value from 21 trading days before.

Can you share some light to this?

Here´s the spreadsheet with sample data (excel 2013):
https://www.mediafire.com/?g44m1f3kpqclkzn

I highly appreciate your help.

 

[scottsen]

I don't doubt there is a way to do this, directly in a measure. I just think it is not a measure I would enjoy writing

What I suggest is to add another column to your calendar that is the "working day number".
=CALCULATE(SUM(Calendar[WorkingDays]), Calendar[FullDate] <= EARLIER(Calendar[FullDate]))

This gives you an easy value to "offset" from when you write your "back 21 working days" measure:

=CALCULATE(
MAX(FactTable[CLOSE]),
FILTER(ALL(Calendar),
Calendar[WorkingDayNum] = MAX(Calendar[WorkingDayNum]) - 21
)
)

I admit to being curious what the "direct" measure would look like... but not curious enough to work on it close to midnight

 

[nicoan]

Hi Scott

*Perfect*! Thanks a million.
My karma points to you

 

[nicoan]

I noted that after I added the "Working Day Number" calculated column, the Powerpivot window takes minutes to open, making it super slow.

Is there any way to have the same results without a calculated column?
We could put that column in the Calendar sheet instead, for example.

What would be the equivalent to this formula in excel?
=CALCULATE(SUM(Calendar[WorkingDays]), Calendar[FullDate] <= EARLIER(Calendar[FullDate]))

Or maybe doing it all directly in a calculated field as mentioned?

 

[scottsen]

I put that calc column on the Calendar table, it did take awhile to build (so, I expect data refresh would be a bit slow), but I'm surprised you noticed any other impact. If you can include that column with however you generate your calendar table... that would be the best.

I'll try to find time to write the calc field... it some sorta "filter the calendar table down to rows that make the sum of calendar[IsWorkingDay] equal to 15, if you include the rows between the current date... and uh, the dates you are filtering on"... but ****.

 

[nicoan]

The thing is that the calendar is much longer than the sample and the FactTable is kind of huge, with several calculated fields. I think that´s making it slow.

I agree that it would be best (and easier maybe) to have it in the Calendar (linked) sheet within excel (outside Powerpivot). But I don´t know how the formula would be.

Any idea?

Again, thanks so much for the replies. I highly appreciate it.