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.
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.
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’))
@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.
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
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?