YTD Expense Sum Spanning Multiple Years

[RHAccTemp]

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!

 

[scottsen]

I am trying to wrap my head around what you want your measure to return. It's not really a YTD, which sorta "resets to zero" at the start of each year, by definition.

Maybe you are looking for a Lifetime To Date? (which doesn't exist natively, but is easy to write)
Lifetime To Date

 

[RHAccTemp]

Yes, I'm seeking a life to date formula. I think the issue is around the use of dates. I have a [Date] column in my 'Act Expense' table, and the dates are month end dates. However, I'm not using the dates in the pivot table display. My pivot table is displaying cost center, account number, and account description (as row labels).

If I use a formula such as

LTD:=calculate(sum('Act Expenses'[Act Amount]),filter(all('Date Table'),'Date Table'[Date]<=max('Date Table'[Date]))

I receive an Excel error message. Any suggestions ?

 

[scottsen]

Lets start with... What does the error say?

 

[RHAccTemp]

I figured it out. I did not have a third closing parenthesis.

I do have another question. What is the proper DAX syntax for an IF statement. What I am seeking is if the slicer for only 2014 is chosen, I want to use the following formula:

TotalYTD(sum('Act Expenses'[Act Amount]),'Date Table'[Date],all('Date Table'[Year]))

If the slicer for only 2015 is chosen, I want to use the following formula:

TotalYTD(sum('Act Expenses'[Act Amount]),'Date Table'[Date],all('Date Table'[Year]))

If the slicer for 2014 and 2015 is chosen, I want to use the following formula:

Calculate(Sum('Act Expenses'[Act Amount]),filter(all('Date Table'),'Date Table'[Date]<=max('Date Table'[Date])))

Thanks