How to find value for first and last row in selected time period in PowerPivot 2013


  • I have a facts table named Results (ResultID int, OrderOpenTime datetime, AccountBalance decimal, AccountEquity decimal, DateKey date) and a DimDates table with various time dimensions in PowerPivot 2013. The Results table is updated about every 5 minutes with the latest account and equity number.

    I cannot figure out how to set up a calculated field to return the value of the first (or last) AccountBalance per time period. Regardless of what time dimension is used in the pivots, the measure should be able to find the first and last AccountBalance since OrderOpenTime is also included, but I can't make the measure work.

    Could someone please help me with this while I still have some hair left?




    Update: I've added a measure to the Results table called FirstRowDateTime that correctly gives me the first row for the selected time period. Now I need a =calculate([AccountBalance), ... where the filter clause sets [OrderOpenTime]=[FirstRowDateTime]

    By my limited understanding =CALCULATE(SUM([AccountBalance]);FILTER(Results; Results[OrderOpenTime]=[FirstRowDateTime])) should calculate the sum of only one raw, but I get the sum of all [AccountBalance] as if the FILTER statement wasn't even there.

    • Edited by sgude1 Monday, November 04, 2013 8:02 PM Update
    Sunday, November 03, 2013 7:51 PM


All replies