Calculating YTD and Previous Period YTD effectively in the same Pivot

Question

I am working on a PowerPivot file where I have to display specific measures by Current Fiscal YTD and Previous YTD in the same table across 10 different measures.  I am struggling to find the best approach for generating the results I need.  While I can Create YTD measures using this (same with using parallel period function for previous) 

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

I don't want to have to create 20 separate calculated meaures to get this result:

                 FY 2011 TD     FY 2012 TD

Measure1     123               234

Measure2      50                111

Measure3       23               34

....

In the underlying data I have a FactTable containing the values to sum and a dimTime table with Date, Fiscal Month, Fiscal Quarter, Fiscal Year.  As per best practice the dimTime isn't limited in its timespan.  I have an ID in the Fact that joins to DimTime.

Would the best approach be to add a calculated column to the dimTime table for something like "current YTD reporting period"  then have an IF statement evalulate the Date to determine 1 is Current period or 0 is not current period and then I can filter all measures by this?  And the trick is to ensure it is dynamic so when I go to refresh the following month it knows to pick up those additional dates as current YTD reporting period. 

Appreciate any thoughts and ideas on this. 

Answer 1

Hi N. Edmonds,

It seems there are two questions here - I wanted to be sure I understand what you are looking for:

1) how to create one YTD calculation that is dynamic, depending on the measure name on the row labels

2) configure the YTD calculation to always take the latest 'date' on the data model to categorize what an aggregate based on current (year?).

Are these two the issues you are trying to solve? 

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 2

Javier - That is exactly what I am trying to do, but with Current YTD and Previous YTD.

Answer 3

Hi N. Edmonds

There are two great references that explain how to accomplish each of those goals:

1) The Data Analysis Expressions Intro whitepaper by Howie Dickerman (available here http://powerpivotfaq.com/PowerPivot%20Samples/Forms/AllItems.aspx ).  On this document, you will see a section called "calculating many time periods within a single measure formula" which can be used to create dynamic calculations based on the item currently in context.   You can apply a variation of this technique to alternatively apply a YTD calculation to the current row element in context.

2) This blog entry by Kasper de Jonge (http://www.powerpivotblog.nl/use-powerpivot-dax-to-automatically-report-on-the-last-month-that-has-data ) describes a technique to dynamically select the latest month on a powerpivot model.  You can use this technique as a model to generate a selection on the current date, instead of current month.  For this, you can replace the expression with something like the following:

=IF('Calendar'[Date] = CALCULATE( LASTNONBLANK( Sales[Date], 1 ), ALL(Sales)  ), "Current Date" )

With this, you will get the last date in your fact table in which there is data (sales or otherwise) and tag it as the current date.  You can then use this tag as a selection in a slicer to dynamically default the report to always use the most current date.

---

Javier Guillen
http://javierguillen.wordpress.com/