|Q: In my source data I have daily balance values. How in PowerPivot for Excel can I create calculation that shows correct closing balance for year, quarter and month?|
|Written by Vidas Matelis|
|Sunday, 29 November 2009 23:55|
Q: In my source data I have daily balance values. How in PowerPivot for Excel can I create calculation that shows correct closing balance for year/quarter and month?
A: PowerPivot for Excel has DAX functions ClosingBalanceMonth, ClosingBalanceQuarter and ClosingBalanceYear that could help you implement this functionality.
Example: Lets build a report that shows trading stock closing value for the day, month, quarter and year. This report will contain data from 2 tables:
If you just drop ClosingValue field in the "Values" area, by default that value will be summed. This does not make sense at any level that is higher than a day. You can change fields aggregation type from "SUM" to "Average" and then at the higher level you'll be able to see stock average price for that month, quarter or year. This makes more sense, but still does not produce expected results.To get closing stock value for a month, year and quarter you will need to create calculated measure using DAX. Here is sample formula that will do this calculation:
=IF(CountRows(VALUES('Date'[Date])) = 1,SUMX(Stocks, Stocks[ClosingValue]),ClosingBalanceMonth(SUMX('stocks', 'stocks'[ClosingValue]), 'stocks'[date]))
In this calculation we first check count of rows for current date selection. If value is 1 then that means we need to calculate closing value for the day level. In this example we should expect that there might be more than one stock selected so we are returning SUM of stock values for day, month, quarter or year.
If there is more than one day for current calcualtion, that means this cell is calculated for a date dimension at the level that is higher than day level (for example month, quarter, year or ALL). In such case we can use ClosingBalanceMonth DAX function to get closing balance at the end of the month. Same function works well for quarter and month level, so in this case there is not need to use functions ClosingBalanceQuarter and ClosingBalanceYear. When we calculate Closing balance, we return SUM of stock closing values, as in the report user might have selected more than one stock to analyze. In such case we will return sum of stock values for specific day for selected stocks.
Here is screenshot of the report that shows correct stock value for day, month, quarter and year:
- Portfolio Slicer - free BI application to track your investments in Excel with PowerPivot
- How to solve issue when PowerPivot for Excel crashes on all workbooks
- Using PowerPivot to analyze web access log in "Combined Log" format (CLF)
- Q: I have integer amount that represents duration in seconds. How can show this amount in format "h:mm:ss"?
- DAX cheat sheet
- Step by step guide on installing PowerPivot for SharePoint on a single machine
- List of PowerPivot DAX functions with description
- How to install PowerPivot for Excel and list of know issues
- List of suggested datasets to test PowerPivot
- Q: How can I calculate difference between two dates in DAX (seconds, minutes, hours, days and months)
- Learning PowerPivot and DAX
- Using Power Pivot and Power View for Profit Analysis
- DAX cheat sheet