When you have to perform time-related calculations in Power Pivot or in Analysis Services Tabular, you have to rely on some DAX expressions. There are specific functions grouped in a “Time Intelligence” section in the on-line documentation, but it’s a good idea learning a few basic concepts at the beginning, since these functions require a Date table in the data model and all of them simply applies more complex filters to a CALCULATE expression. Knowing that, you will avoid a few pitfalls like the one involved in performing time intelligence calculations within a row context and you will be able to perform week-based calculations (which are not supported by native time intelligence functions in DAX).
If you want to learn these concepts from the ground up by reading a longer and detailed description, you can read either the SSAS Tabular or the Power Pivot book. Or you can get one hour of your time and watch the full Time Intelligence video, where Alberto explains in detail how to use these functions. You can access to the entire video library available on Project Botticelli buying a monthly subscription (you can also register for just one month – no long term commitment is required). If you are interested, a special 15% discount is available until end of September 2013 using the following discount code: SQLBI2013SEP. Otherwise, a 10% discount is available until end of 2013: SQLBI2013.
Evaluates an expression in a context modified by filters.
CALCULATE ( <Expression> [, <Filter> [, <Filter> [, … ] ] ] )