Calculate Date Field for "In Last 7 Days"

# Calculate Date Field for "In Last 7 Days"

• 2012년 3월 13일 화요일 오후 4:59

I have a typical Dates table in my PowerPivot file - the usual Date column with one row for each day. I'd like to add a calculated field to each row called "InLast7Days". The goal would be that the 7 rows in the Dates table would have TRUE, and the rest would be FALSE. The 7 rows that are TRUE would be strickly based on the actual calendar date (not some filtered view depending on which dates the user has selected in their pivot table, etc.).

The goal of all of this is that we need to have pivot table that everyday shows the absolute last 7 days of history in a dashboard-like view. Of course, we don't want our users to have to manually re-select the last 7 days in the date dimension in the filters for the pivot table - it should always just show the 7 days (day by day). If we wanted MTD or YTD, I know we could use the built-in date filters (in PowerPivot 2012), but there's no option for Last 7 Days (or Last 30 days, etc.).

Of course, if I'm missing something and there's a better way, please let me know that, too.

Chris

### 모든 응답

• 2012년 3월 15일 목요일 오전 9:06

Hi Chris,

I have a case where the client wanted to see open salesorder by period (older then 30 days, within 0 - 6 days, within 7 -13 days ...). I created calculated measues like this:

Within 7 - 13 days =CALCULATE(SUM(SalesOpen_Currency[Qty]),DATESBETWEEN(Time[DATE],TODAY()+7,TODAY()+13))

Maybe you could use something like  IF(Datesbetween, today()-7,today(),true,false)

Larra

• 답변으로 표시됨 2012년 3월 15일 목요일 오후 3:19
•
• 2012년 3월 15일 목요일 오후 3:19

Larra,

Thanks - I got it working! I was trying to use the DATEADD formula instead of the more-simple Today()-7. Once I got that figured out, everything fell into place.

Chris