locked
Calculating YTD Values from financial periods RRS feed

  • 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

Answers

All replies

  • i blogged about this issue some time ago:
    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 Charlie Liao Wednesday, February 5, 2014 7:32 AM
    • Marked as answer by Charlie Liao Thursday, February 13, 2014 1:55 AM
    Tuesday, February 4, 2014 10:13 PM
    Answerer
  • 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