I'm working with PowerPivot in Excel 2013. Data is loaded from SQL Server 2012.
I have a data table named Results (ResultID int, OrderOpenTime datetime, AccountBalance decimal, AccountEquity decimal, DateKey date) and a lookup table DimDates with various time dimensions. The Results table is updated about every 5 minutes with the latest account and equity number which is good for trend pivots, but I have a problem with grouping to show results.
I've added measures to the Results table called FirstRowDateTime and LastRowDateTime that correctly gives me the first and last row for the selected time period, but I'm unable to use them for filtering.
By my limited understanding
should calculate the sum of only one row (the first one in the chosen time period of the pivot table), but instead I get the sum of all [AccountBalance] as if the FILTER statement wasn't even there.
What am I missing? I've included a spreadsheet with sample data.