YTD figures in Power Pivot

Question

Hi,

I imported a standard calendar from http://www.powerpivotpro.com/2011/11/the-ultimate-date-table/ in Power Pivot 2013.  I have another table (Feedback) in Power Pivot which has a Date column.  Each date in the date column of the Feedback Table is one activity - there are duplicate dates in the Date column of the Feedback Table - representing individual participant feedback on that given date.  So if 13 participants provided feedback on the activity performed on 25/1/2012, then 25/1/2012 will appear 13 times in the Date column of the Feedback Table.  I created a relationship between the date column in Feedback table and date column in the Calendar.  I then created a Pivot Table in Excel 2013 - dragged YearKey column (from the calendar Table) to the Row labels.

I created a calculated Field formula in the Pivot Table to ascertain the Unique activities per Calendar year =calculate(DISTINCTCOUNT(Feedback[Date]))

The result of this is absolutely correct.

In the same Pivot Table, I would like to determine the Unique activities per Fiscal year.  The fiscal Year runs from April to March.

Please help with the formula for this.

---

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Answer 1

Ashish -

The TOTALYTD function should work for you.  It takes an optional third parameter for end of year that you can leverage for your fiscal calcs.  Try this:

=TOTALYTD([UniqueActivities],Calendar[Date],"03-31")

You may need to tweak that to fit your model's table and measure names, but hopefully that helps. 

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 2

Hi,

Thank you for your reply.  This does not give the correct Answer.  I entered the following formula

=TOTALYTD([Distinct sessions],Calendar[DateKey],"31-3")

While the formula does not yield any error, the result that I get it wrong.  On checking the result, I realized that it is counting Distinct sessions from 1 April to 31 December.  It should count from 1 April to 31 March of the years mentioned in the Row labels.

=CALCULATE([Distinct sessions],DATESYTD([DateKey],"31-3"))

Both formulas give the same result.

Even when I change 31-3 to 3-31, the result is the same.

Please help.

 

 

---

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Answer 3

Hi,

I solved the problem by doing this

=[Sessiosn from 1 April to 31 Dec of this year]+[Sessiosn from 1 Jan to 31 Mar of next year]

[Sessiosn from 1 April to 31 Dec of this year] was computed from

=TOTALYTD([Distinct sessions],Calendar[DateKey],"31-03")

[Sessiosn from 1 Jan to 31 Mar of next year] was computed from

CALCULATE([Distinct sessions],NEXTQUARTER(Calendar[DateKey]))

I am still looking for a single formula solution.

---

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Answer 4

That's strange.  The formula I posted above works perfectly for me, as long as I have a full date table covering all of the date ranges to be analyzed.

I'd suggest adding a couple of calculated fields to troubleshoot:

STARTOFYEAR(LASTDATE(Calendar[Date]),"3-31")
ENDOFYEAR(LASTDATE(Calendar[Date]),"3-31")

Putting those in your pivot should show you exactly what date range your YTD measure is using.  Here's a link to a sample 2013 file on my SkyDrive with some worked examples I put together for testing.

Also, for more details on these time intelligence functions, this post from Jeffrey Wang is one of the best I've seen explaining the underlying logic and some of the things to watch out for.  Let me know if that helps.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 5

Hi,

Thank you for replying.  This formula worked well

=CALCULATE([Distinct sessions]
                        ,DATESBETWEEN(Calendar[DateKey]
                                                        ,STARTOFYEAR(LASTDATE(Calendar[DateKey]),"3-31")
                                                        ,endOFYEAR(LASTDATE(Calendar[DateKey]),"3-31")
                                                       )
                       )

---

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Answer 6

Hi,

Thank you for helping me on this so far.  I have another question - I would now like to determine the session conducted from inception till the financial year should on the column label of the Pivot Table

So in the column labels of the Pivot Table there are years from 2007 till 2013 in range F4:K4.  In the Data area of the Pivot Table there are sessions conducted in the Fiscal Year (Ending 31 March 2013) in range F5:K5 (the formula for this is in my previous post).  All well so far.

Now in F6:K6, I would like to determine the sessions from inception till the years shown in range F4:K4.

Please help with the formula for this.

Thank you.

---

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Answer 7

Glad that helped Ashish. 

And for Inception-to-date, just need to pass BLANK() to DATESBETWEEN for the start-date parameter, instead of the start of year you used in the YTD calc.  Like this:

=CALCULATE([Distinct sessions]
                        ,DATESBETWEEN(Calendar[DateKey]
                                                        ,BLANK()
                                                        ,endOFYEAR(LASTDATE(Calendar[DateKey]),"3-31")
                                                       )
                       )

Let me know if that helps.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 8

Hi,

Thank you.  That got me exactly what I wanted.  When I used your formula, I got the running count for all years till 2100 (last year in the Calendar table.  So to get the running count only till the current year, I used this modified version

if([Fiscal Year distinct sessions]=0,BlANK(),CALCULATE([Distinct sessions]
                        ,DATESBETWEEN(Calendar[DateKey]
                                                        ,BLANK()
                                                        ,endOFYEAR(LASTDATE(Calendar[DateKey]),"3-31")
                                                       )
                       ))

I used the following formula to determine the Fiscal Year distinct sessions

=CALCULATE([Distinct sessions]
                        ,DATESBETWEEN(Calendar[DateKey]
                                                        ,STARTOFYEAR(LASTDATE(Calendar[DateKey]),"3-31")
                                                        ,endOFYEAR(LASTDATE(Calendar[DateKey]),"3-31")
                                                       )
                       )

Is there an alternate way to accomplish this?

Thank you once again.

---

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com