# Calculating YTD Values from financial periods

• ### Question

• Hi there, I’m making my first steps with PowerPivot and have come across a snag that I can’t seem to get past. We’re working with financial periods here in the format YYYY.PP where YYYY.00 is the balance brought forward and YYYY.12 is period 12 of the current year.

I’ve got a pivot table with two dimensions, a filter in the above format (PK_FinancialPeriod) and finance codes down the side (Nominal). These both come from the same table (PeriodBalancesByNominal). I’m trying to get a year to date Balance from YYYY.01 to YYYY.PP as selected in the filter.

The following formula gives me the answer I want, but it’s not very reusable as I’d have to specify the fields I didn’t want to filter (which could change):

```=CALCULATE(
SUM(PeriodBalancesByNominal[Balance]),
FILTER(ALLEXCEPT(PeriodBalancesByNominal, PeriodBalancesByNominal[Nominal])
,PeriodBalancesByNominal[PK_FinancialPeriod]<=EARLIER(PeriodBalancesByNominal[PK_FinancialPeriod])
),
FILTER(ALLEXCEPT(PeriodBalancesByNominal, PeriodBalancesByNominal[Nominal])
,PeriodBalancesByNominal[PK_FinancialPeriod]>
left(EARLIER(PeriodBalancesByNominal[PK_FinancialPeriod]),find(".",EARLIER(PeriodBalancesByNominal[PK_FinancialPeriod])))&"00"
)
)```

The following formula just gives me the current period’s values:

```=CALCULATE(
SUM(PeriodBalancesByNominal[Balance]),
FILTER(ALL(PeriodBalancesByNominal[PK_FinancialPeriod])
,PeriodBalancesByNominal[PK_FinancialPeriod]<=EARLIER(PeriodBalancesByNominal[PK_FinancialPeriod])
),
FILTER(ALL(PeriodBalancesByNominal[PK_FinancialPeriod])
,PeriodBalancesByNominal[PK_FinancialPeriod]>
left(EARLIER(PeriodBalancesByNominal[PK_FinancialPeriod]),find(".",EARLIER(PeriodBalancesByNominal[PK_FinancialPeriod])))&"00"
)
)```

In my simplistic view of things, with two dimensions, the first formula stripping off all but Nominal and applying the filter and the second stripping off PK_FinancialPeriod and applying the filter should give me the same result. It isn’t a cube data source (just a straight SQL table) so it can’t be inheriting any context there. Any insight into where I’m going wrong, or a different way of achieving the same result would be much appreciated.

Tuesday, February 4, 2014 9:13 PM

http://blog.gbrueckl.at/2013/02/fiscal-periods-tabular-models-and-time-intelligence/

the idea is to use the built-in time-intelligence functions like TOTALYTD()
in order to do this you need to have a Date-Table on a daily level which can also be used to handle your financial periods

hth,
gerhard

Gerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com

• Proposed as answer by Wednesday, February 5, 2014 7:32 AM
• Marked as answer by Thursday, February 13, 2014 1:55 AM
Tuesday, February 4, 2014 10:13 PM

### All replies

http://blog.gbrueckl.at/2013/02/fiscal-periods-tabular-models-and-time-intelligence/

the idea is to use the built-in time-intelligence functions like TOTALYTD()
in order to do this you need to have a Date-Table on a daily level which can also be used to handle your financial periods

hth,
gerhard

Gerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com

• Proposed as answer by Wednesday, February 5, 2014 7:32 AM
• Marked as answer by Thursday, February 13, 2014 1:55 AM
Tuesday, February 4, 2014 10:13 PM
• Hi Gerhard, thanks for this, it's really useful. I'm still struggling with when this is filtered (as YTD will only show the currently filtered month) but it's got me some of the way.

Cheers!

Dave.

Friday, February 14, 2014 5:50 PM