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
No, I solved it with OpeningBalance:=SUMX(TOPN(1;Results;Results[OrderCloseTime];1);[AccountBalance])
you may also use LASTDATE() or FIRSTDATE()
Power Pivot/DAX also have predefiend functions to handle opening and closing balance scenarios and also various other time-intelligence scenarios:
- www.pmOne.com -
- Proposed as answer by Jason Tom ThomasEditor Monday, November 18, 2013 1:38 PM