PowerPivotPro's FAQ Site

To create a measure that shows you the sum of values of the running month even when you have future data in your time dimension you have to use the function LASTNONBLANK. usage: LASTNONBLANKDATE (,[]) Returns the last date in the current context for the specified Date_Column. When the optional argument, expression, is supplied, the function returns the last date where the conditions in the expression return a non-blank value. So using the expression you need to return null/blank where no related data is available like: LASTNONBLANK(DATEFIELD, countrows(RelatedTable(FACTTABLE))) We count the rows of the fact table related to the datefield supplied. When no rows are counted the last date supplying rows is the lastnonblank. Sample of usage measure Total Last month of year: =CALCULATE(sum(‘FactInternetSales’[OrderQuantity]), DATESMTD( LASTNONBLANK(‘DimDate’[FullDateAlternateKey], countrows(RelatedTable(‘FactInternetSales’)))) ,ALL(‘DimDate’)) (Use the ALL in CTP3 only).