PowerPivotPro's FAQ Site

Say that you have a pivot table with regions across the top and daily dates down the side.

 

To calculate MTD sales for this region, use:

=Calculate(Sum(Fact[Sales]),

    DatesMTD(Fact[Date]),

    AllExcept(Fact,Fact[Region]))

 

Breaking this down:

The expression asks for sum of sales.

Filter 1 says for all dates up included in MTD of this row

Filter 2 says to respect the current column's filter for region.

 

If instead you wanted MTD sales across all regions:

=Calculate(Sum(Fact[Sales]),
    DatesMTD(Fact[Date]),
    All(Fact))

 

If you wanted to see this region's MTD sales as a percentage of total MTD sales:

=Calculate(Sum(Fact[Sales]),
    DatesMTD(Fact[Date]),
    AllExcept(Fact,Fact[Region]))/

Calculate(Sum(Fact[Sales]),
    DatesMTD(Fact[Date]),
    All(Fact))

MTD is only one example. You can extend this concept to any of the Time Intelligence functions. See a complete list of time intelligence functions at the bottom of Vidas Matelis' list of DAX Functions: http://powerpivot-info.com/post/53-list-of-powerpivot-dax-functions-short