Using Time intelligent PowerPivot functions with fiscal year

I had a question by Brad at my Introduction to time intelligent functions in PowerPivot Screencast. He wanted to know if we can use the PowerPivot Time intelligent functions when we have a fiscal year that starts at Jul-Jun in combination with YTD.

The answer to that is: yes we can :). The DATESYTD function has an optional argument year end date. So you can add the end of the year at the parameter.  This will look like:

 =CALCULATE(
sum(‘Tablix1′[nroforders])
, DATESYTD(‘Date'[Date],”06-01″)
,ALL(‘Date’))

This function will get the sum of nroforders of the entire year until the current month starting at 06-01 of a year, remember the All date that we need to use in CTP3.

This will result in the following pivottable, as I have no data of 2007 in my dataset PowerPivot will starts at 1/1/2008 (first date available) but you see the year starts again in July:

There are a number of other functions you can use this parameter, like nextyear and TotalYtd.

  • Brad

    Thanks Kasper, Thats exaclty what I am after. I have only been looking at powerpivot for 2 days and now am re-thinking our entire delivery platform as I can see many benefits.

  • Brad

    Kasper,
    Sorry accidently posted this in the other article as well.

    Trying to get this working with Australian date format and am having problems.
    In the powerpivot windows I have created the date column and it correct shows the value 1/7/09 as 1-Jul-09. However when I try and use the pivot on the spreadsheet it seems to be interpreting as US format of month/day/year and displays as 7/1/2009.

    Row Labels Sum of Actuals YTD
    7/1/2009 2.01907E-09 0.00
    8/1/2009 3.03544E-11 0.00
    9/1/2009 -2.6148E-12 0.00
    10/1/2009 3.06386E-11 0.00
    11/1/2009 6.82121E-11 0.00
    12/1/2009 4.66116E-11 0.00
    1/1/2010 1.72236E-11 0.00
    2/1/2010 0 0.00
    3/1/2010 2.27374E-13 0.00
    4/1/2010 2.27374E-13 0.00
    5/1/2010 2.27374E-13 0.00
    6/1/2010 2.27374E-13 0.00
    Grand Total 1.52113E-09 2.44995E-11

    My YTD formula is :
    =CALCULATE(sum(‘Query’[Actuals]),DATESYTD(‘Query’[theDate],”06-01″),all(‘Query’))

  • Kasper de Jonge

    @Brad
    Hi Brad,

    Since i’m from the Netherlands we also use the dd-mm-yyyy.
    PowerPivot sees my date as dutch date in the powerpivot window (as does your PowerPivot).

    When i put the date at the pivot table the date will be shown in US format. I cannot make it use the dutch format. I’ll try and figure out how we can achieve this.

    But the good news is that I have created the measure again with the dutch language settings:
    =CALCULATE(sum(‘Tablix1′[nroforders]), DATESYTD(‘Tablix1′[Date],”01-06″),all(‘Tablix1’))
    where 01-06 is the first of january in the dutch format. So this appears to work. I understand this is confusing. The dates should be shown in the native language setting.

    Conclusion: Time intelligent functions use the regional format, but the pivottable doesn’t show it in your regional setting. I’ll see what i can find out regarding the layout.

  • Brad

    Kasper,

    I am still having a couple of problems getting my number correct with this. Could you please email me a sample spreadsheet. if possible. thanks

  • Brad

    Kasper,
    I am getting further and after experimentation I have got the examply you have working.
    My previous problem seems to be related when my fact data comes from a cube. The date coming from the cube is text field with date/time stamps. In powerpivot I use some calculated columns to generate a date column .

    when using data from linked tables in the workbook:
    CalYear CalMonth Sum of Actual DATaYTD
    2008 180 180
    1 10 10
    2 10 20
    3 10 30
    4 10 40
    5 10 50
    6 10 60
    7 20 80
    8 20 100
    9 20 120
    10 20 140
    11 20 160
    12 20 180
    2009 180 180
    1 20 20
    2 20 40
    3 20 60
    4 20 80
    5 20 100
    6 20 120
    7 10 130
    8 10 140
    9 10 150
    10 10 160
    11 10 170
    12 10 180
    2010 60 60
    1 10 10
    2 10 20
    3 10 30
    4 10 40
    5 10 50
    6 10 60
    Grand Total 420 60

    However this raises another problem with I try and present the data by financial year.

    Fin Year Sum of Actual DATaYTD
    FY 2008 60 60
    07 January 10 10
    08 February 10 20
    09 March 10 30
    10 April 10 40
    11 May 10 50
    12 June 10 60
    FY 2009 240 120
    01 July 20 80
    02 August 20 100
    03 September 20 120
    04 October 20 140
    05 November 20 160
    06 December 20 180
    07 January 20 20
    08 February 20 40
    09 March 20 60
    10 April 20 80
    11 May 20 100
    12 June 20 120
    FY 2010 120 60
    01 July 10 130
    02 August 10 140
    03 September 10 150
    04 October 10 160
    05 November 10 170
    06 December 10 180
    07 January 10 10
    08 February 10 20
    09 March 10 30
    10 April 10 40
    11 May 10 50
    12 June 10 60
    Grand Total 420 60

    as you can see the data of actuals is correct however the YTD data now goes very stange:
    any idea’s?

  • J Holterman

    Kasper,

    This Function works fine as long as each Month has records.
    In my sheet, one month has no data.
    For that specific Month the YTD returns with a zero.
    Any idea how to solve that?

  • J Holterman

    Kasper,
    You can download the sheet with this example on:
    http://home.kpn.nl/j.m.holterman/SheetsOnline/PowerPivotYTD01.xlsx

    kind regards, Jeroen

  • Hi

    Thanx for a great post, how what would the last day of the year be, if the year ends in Feb, taking into consideration that Feb is a leap year at times. I’ve tried many of options and I cant get the Fiscal Year to date to calculate properly, it does work if i use the 28 Feb as the last day of the year however when it is a leap year the 29th goes onto the new year. if I use the 29 Feb as the last day of the year the last day of the year is set to the 1st Feb, really confusing, I am not sure what i am doing wrong.

    My DAX Calc as follows
    Cal Sales Amount FYTD:=CALCULATE(Sum(Fact_Sales[TotalExclAfterDisc]),DATESYTD(Dim_Date[The_Date],”29 Feb”))

    Any ideas?
    Regards, AQ

  • Rajiv Singh

    Hello All,

    My custom year YTD is not working properly. I am looking from July – June Fiscal year

    https://uploads.disquscdn.com/images/db02616d295343c619473d03ab157748a36a5e8c3c719def07121742d8b7bc42.png

    • Rajiv Singh

      Sorry my bad its working.
      correct formula: YTD custom:=CALCULATE(SUM(‘Transaction'[CurrentSalePriceLocal]), DATESYTD(‘Date'[PK_Date],”06-30″), ALL(‘Date’))

  • Rajiv Singh