This topic contains 6 replies, has 3 voices, and was last updated by 
-
Posts
-
Hoping you can point me in the right direction on this issue I’m having. In the attached example I am trying to determine YTD and PYTD sales revenue. In the measures I created the total for YTD is fine (because there are no values for the second half of the year). For the PYTD it’s giving me the full prior year… not sure what I’m doing wrong. I want to be able to display in a pivot table.
I included a manual check of the figures and a CUBEVALUE approach that does give me the right values.
Can you help?
Hi Scott,
The default behavior in Power Pivot for YTD and PYTD is to run until the end of the year, which results in YTD values repeating for the last active month and PYTD values appearing in cells of future periods (in other words, in August 2016, September 2016 is still in the future, but a PYTD measure will still display a September 2015 value).
Often when calculating YTD and PYTD for revenue, the filter context of a cell is checked for revenue > 0, and if not, then the calculation for YTD or PYTD is not evaluated. In such a case, pivot grand totals are also turned off (Pivot Table tools > Design > Grand Totals > Turn off for Columns and Rows).
However, if you would like to evaluate “grand total only” measures for YTD and PYTD, let me know and I will put them into your workbook and attach.
Tom
Thanks Tom, that makes sense to me. I would like to see the solution for “grand total only” if you wouldn’t mind sir.
Thanks in advance!
Scott,
Please see attached workbook for example measures that generate and work with YTD and PYTD grand totals.
While the exact formulas may not fit every pivot, I believe the patterns will.
Tom
Hi Tom!
I use Excel 2010 so I can’t see the measures in the file you shared to Scott. Any chance you can copy them here or share a file for Excel 2010?
Thanks!
plrd,
Have copied measures into attached text file.
Tom
Thanks a lot!
The forum ‘Power Pivot’ is closed to new topics and replies.