Hi,
My powerpivot output is currently summarised as follows (the desired column is the result i would like):
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col span="3" style="text-align: center;"></colgroup><tbody>
</tbody>
The 2 measures i have calculated are as follows:
Balance Sheet =CALCULATE(sum(BalanceSheet[Accrual (£)]))
Movement =CALCULATE(sum(BalanceSheet[Accrual (£)]),PARALLELPERIOD('Calendar'[Date],-1,MONTH))-[Balance Sheet £]
So i'm calculating the month movement on the balance sheet.
My data set begins in Sep-15 and will be updated on a monthly basis - the last set of data i have is Sep-16.
I would like the first month's movement to return blank (Sep-15 in this case) as essentially it's calculating current month - prior month balance sheet when there is no prior month's balance sheet.
Similarly, i would like to return a blank in Oct-16 as it's not a real value (i don't have any data for Oct-16).
The only complication is that my data set is at a product level and we have new products come in each month so if i pivot by product i don't want to return a blank if the product didn't exist in the prior month.
Is there a way to return the blanks i desire?
Thanks
My powerpivot output is currently summarised as follows (the desired column is the result i would like):
FinancialYear | MonthName | Balance Sheet | Movement | Desired |
2015 | Sep | 1,344 | 1,344 | |
Oct | 1,570 | 226 | 226 | |
Nov | 1,486 | -84 | -84 | |
Dec | 1,526 | 40 | 40 | |
Jan | 1,079 | -447 | -447 | |
Feb | 1,480 | 401 | 401 | |
Mar | 1,882 | 402 | 402 | |
2016 | Apr | 1,051 | -831 | -831 |
May | 1,880 | 829 | 829 | |
Jun | 1,549 | -331 | -331 | |
Jul | 1,691 | 142 | 142 | |
Aug | 1,740 | 49 | 49 | |
Sep | 1,764 | 24 | 24 | |
Oct | 1,764 |
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col span="3" style="text-align: center;"></colgroup><tbody>
</tbody>
The 2 measures i have calculated are as follows:
Balance Sheet =CALCULATE(sum(BalanceSheet[Accrual (£)]))
Movement =CALCULATE(sum(BalanceSheet[Accrual (£)]),PARALLELPERIOD('Calendar'[Date],-1,MONTH))-[Balance Sheet £]
So i'm calculating the month movement on the balance sheet.
My data set begins in Sep-15 and will be updated on a monthly basis - the last set of data i have is Sep-16.
I would like the first month's movement to return blank (Sep-15 in this case) as essentially it's calculating current month - prior month balance sheet when there is no prior month's balance sheet.
Similarly, i would like to return a blank in Oct-16 as it's not a real value (i don't have any data for Oct-16).
The only complication is that my data set is at a product level and we have new products come in each month so if i pivot by product i don't want to return a blank if the product didn't exist in the prior month.
Is there a way to return the blanks i desire?
Thanks