none
Calculate Date Field for "In Last 7 Days"

    Dotaz

  • 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.

    Thanks in advance.


    Chris

    13. března 2012 16:59

Odpovědi

  • 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

    15. března 2012 9:06

Všechny reakce

  • 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

    15. března 2012 9:06
  • 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

    15. března 2012 15:19