PowerPivot function/Target year Slicer issue

Question

I have a situation in my excel/powerpivot dashboard in which I want to calculate value for a revenue funnel for: Same Month Last Year.

To do so the ParallelPeriod function works perfect for me, however, the results only show when I have marked this year as well as last year in my Year slicer.

eg: To calculate value for the dashboard for April, I need to do the comparison for the same period last year as well.

April 2015 vs April 2014:

SAME MONTH Last Year:=CALCULATE(sum([Rev. wgt (k$)]),PARALLELPERIOD(DimDate[Date],-12,MONTH))

and then I compare with this month:

This Month vs Same Month LY:=((sum([Rev. wgt (k$)])- ([SAME MONTH Last Year])) / [SAME MONTH Last Year])

To Calculate April 2014, I use the ParallelPeriod function. But to have the value in the dashboard, I have no other option but to select multiple year combination in the year slicer to get results. (See image, pls)

I want to avoid using the Year slicer and want that slicer only to have current year (2015) selected for result. I do not want 2014 to be marked as it changes some of my other views which are yearly focused.

Therefore, is their any way I can use only months or any other formula to show the result for "Same Month Last Year" without marking the previous year (2014) in the year slicer? I want avoid using the target year slicer to return value.

Any suggestions, guidance would be highly appreciated.

mmarie mmarie · Accepted Answer · 2015-05-07T03:03:47

If you are using a standard calendar, you can do this:

Same Period Last Year:=CALCULATE( SUM('FactTable'[Rev. wgt (k$)] ),
                         SAMEPERIODLASTYEAR( 'Date'[Date] ) )

That will actually work whether you need last year, same quarter last year, or same month last year.

Here is an answer where I provided slightly different same period last year calculations. A good explanation of the DAX time comparison calculations can be found at http://www.daxpatterns.com/time-patterns/

My formula for sales same month prior year is:

Sales Same Month Prior Year:=CALCULATE([Total Sales], Filter(All('Sale Date'),
'Sale Date'[Month Seq] = max('Sale Date'[Month Seq])-12))

I had to use that formula because my fiscal calendar was not the same as the normal calendar year. The DAX patterns link and my answer to the other SO question explain the Month Seq field.

Be aware that parallel period will always return whole periods regardless of where you are in the current month. That may or may not be desired behavior. From MSDN:

The PARALLELPERIOD function is similar to the DATEADD function except that PARALLELPERIOD always returns full periods at the given granularity level instead of the partial periods that DATEADD returns. For example, if you have a selection of dates that starts at June 10 and finishes at June 21 of the same year, and you want to shift that selection forward by one month then the PARALLELPERIOD function will return all dates from the next month (July 1 to July 31); however, if DATEADD is used instead, then the result will include only dates from July 10 to July 21.

PowerPivot function/Target year Slicer issue

1 Answers