locked
Calculate Date for 8 weeks prior RRS feed

  • Question

  • I'd like to create a calculated measure that averages a column over the previous 8 weeks.  I've tried a bunch of the date functions, but can't quite get the proper formula.  I can resort too changing the View that I pull from SQL Server to create a new column based on my existing WeekEnding date as such:

    DATEADD(WEEK,-8,f.WeekEnding) AS [WeekEnding8WeeksAgo]

    Then I can use the  DATESBETWEEN   and use the WeekEnding and WeekEnding8WeeksAgo as the start/stop dates.  But I'd really rather try to figure out how to do this with DAX in PowerPivot.

    Suggestions?

    Monday, May 14, 2012 11:12 PM

Answers

  • It's a bit difficult to answer without knowing how your data is setup.

    Have you tried something like:

    Measure1:

    =CALCULATE(AVERAGE(Table1[Column1]),
               DATESBETWEEN(DimDate[Date],
                            LASTDATE(DimDate[Date]-56,
                            LASTDATE(DimDate[Date])
               )
     )
    Please post samples of your tables and describe their relationships.

    Tuesday, May 15, 2012 4:01 AM

All replies

  • It's a bit difficult to answer without knowing how your data is setup.

    Have you tried something like:

    Measure1:

    =CALCULATE(AVERAGE(Table1[Column1]),
               DATESBETWEEN(DimDate[Date],
                            LASTDATE(DimDate[Date]-56,
                            LASTDATE(DimDate[Date])
               )
     )
    Please post samples of your tables and describe their relationships.

    Tuesday, May 15, 2012 4:01 AM
  • Thanks, ruve1k.  That approach worked out perfectly.

    Monday, May 28, 2012 7:17 PM