How do you define a measure to sum expenses on a year-to-date basis that will span multiple years ?
I have the following 3 tabs in PowerPivot (with column names and a measure name):
1) Act Expense
a) Date
b) Account CC (a concatenation of account number and cost center)
c) Account Description
d) Cost Center
e) Act Amount
f) MEASURE:Act Exp YTD:=TOTALYTD(SUM('Act Expense'[Act Amount]),'Date Table'[Date],all('Date Table'[Year]))
2) Act CC Table
a) Account CC (a concatenation of account number and cost center)
b) Account
c) Account Description
d) Cost Center
3) Date Table
a) Date
b) Month
c) Year
d) Qtr
Relationships have been defined between both the 'Act CC Table' and 'Date Table' to the 'Act Expense' table (i.e. 'Act Expense'[Date] to 'Date Table'[Date] and 'Act Expense'[Account CC] to 'Act CC Table'[Account CC])
My pivot table has a month and year horizontal slicer.
When I have all 12 months selected and year 1 selected, there is no problem - all 12 months year-to-date show up as actual expense.
When I have all 12 months selected and year 2 selected, there is no problem - all 12 months year-to-date show up as actual expense.
When I have all 12 months selected and year 1 and 2 selected, there is a problem - only the year 2 year-to-date actual expense appears in the pivot table.
The goal is to have all expenses for years 1 and 2 to show on a cumulative per month basis (e.g. if months 1 and 2 are selected and years 1 and 2 are selected, I should see the cumulative amount of both months for both years in the pivot table.
Thanks!
I have the following 3 tabs in PowerPivot (with column names and a measure name):
1) Act Expense
a) Date
b) Account CC (a concatenation of account number and cost center)
c) Account Description
d) Cost Center
e) Act Amount
f) MEASURE:Act Exp YTD:=TOTALYTD(SUM('Act Expense'[Act Amount]),'Date Table'[Date],all('Date Table'[Year]))
2) Act CC Table
a) Account CC (a concatenation of account number and cost center)
b) Account
c) Account Description
d) Cost Center
3) Date Table
a) Date
b) Month
c) Year
d) Qtr
Relationships have been defined between both the 'Act CC Table' and 'Date Table' to the 'Act Expense' table (i.e. 'Act Expense'[Date] to 'Date Table'[Date] and 'Act Expense'[Account CC] to 'Act CC Table'[Account CC])
My pivot table has a month and year horizontal slicer.
When I have all 12 months selected and year 1 selected, there is no problem - all 12 months year-to-date show up as actual expense.
When I have all 12 months selected and year 2 selected, there is no problem - all 12 months year-to-date show up as actual expense.
When I have all 12 months selected and year 1 and 2 selected, there is a problem - only the year 2 year-to-date actual expense appears in the pivot table.
The goal is to have all expenses for years 1 and 2 to show on a cumulative per month basis (e.g. if months 1 and 2 are selected and years 1 and 2 are selected, I should see the cumulative amount of both months for both years in the pivot table.
Thanks!