PowerPivotPro

PowerPivotPro is Coming to Phoenix

February 20 - 22, 2018

Registration for 2018 Public Training is now open!

AVAILABLE CLASSES

**Use the discount code “3ORMORE” when signing up 3 or more people.

FEBRUARY 20 - 21

Foundations: Power Pivot & Power BI

Instructor: Kellan Danielson

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work! Two Days in our class and you are EMPOWERED!

  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • Taught by Kellan Danielson – PowerPivotPro Partner and Vice President of Client Services
  • You don’t need to be an IT professional – most of our students come from an Excel background

FEBRUARY 20 - 21

Level Up Series: Advanced DAX

Instructor: Ryan Sullivan

The Advanced DAX Course was such a hit in the first half of 2017 that we’ve expanded the course to 2 days!

Overview

  • This advanced DAX training class is taught completely in Power BI Desktop.
  • Students are encouraged to take our Foundations course and have hands on experience with the DAX language.
  • Taught by Ryan Sullivan – Principal Consultant.
  • Class material drawn from usage of Advanced DAX applications while consulting with hundreds of international firms.

FEBRUARY 22

Level Up Series: Power Query for Excel & Power BI

Instructor: Krissy Dyess

The second class in the series is our Level Up Series is Power Query for Excel & Power BI.

  • Students are encouraged to take our Foundations course and have hands on experience with Power Query in Excel or Power BI Desktop.
  • Taught by Krissy Dyess – PowerPivotPro Principal Consultant and Phoenix native!
  • We will cover common to unique business challenges made easy with Power Query’s data wrangling capabilities.
  • Intermediate to Advanced Level Power Query best practices distilled into easy to understand patterns to apply to your most common business challenges.
PowerPivotPro Logo

By Dany Hoter

(Download file here to follow along).

Time intelligence functions are some of the most important functions in DAX.

Being able to compare values between current period and the same period last year is a very common request and one that is a real challenge using native Excel and can easily achieved with DAX.

A typical calculated field might look like:

Sales Last Year:=CALCULATE(
  [Sales]  ,SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey])
)

In quite a few cases once you start using such expressions you will see this annoying error message:

image

The reason is that the time intelligence functions like the one used here SAMEPERIODLASTYEAR require 100% completely consecutive dates in the filter context, with no holes.

The problem is that it is not always clear where is the non-consecutive range of days.

In the attached example, you open the Workbook and everything seems to work fine.

If you try to drag the calculated field [UnprotectedLAstSales] to the pivot you’ll get the error.

There are no active slicers at this point so it is not clear why the error situation is triggered.

The reason is the cross-filtering effect of slicers. Excel is checking by default on each slicer which items are empty based on the pivot(s) content , all the measures used in all pivots and what is filtered in all other slicers.

image
Not just the Pivot, the cross-filtering of slicers may yield
Non-Consecutive dates and hence this error

Cure

If you go to slicer settings in the two slicers and uncheck the setting: “Visually indicate items with no data”, you will be able to drag the calculated field from the last step and will not see the error.

clip_image003

If you try and filter a single value in either one of the slicers the error message will reappear.

This time it is rather easy to explain why.

When you select one day of the month let’s say the first of the month, the actual set of days will include the first of each month across a few years span.

This is obviously a non-consecutive range and that’s why the error appears.

In the same way when Excel checks each member in the slicer to see if it is empty it actually uses a non-consecutive range of days for each day of the month.

Total Cure for the Problem

In order to make sure that this error does not appear ever we can change the calculation in this way:

We’ll add a new calculated field:

Holes:=value(lastdate(DimDate[FullDateAlternateKey]))-value(firstdate(DimDate[FullDateAlternateKey]))+1-countrows(dimdate)

This field will return 0 if the list of days is consecutive.

Using it I protect the original field and return blank if the range is not consecutive:

Last Year Sales:=if([Holes]<>0,BLANK(),CALCULATE([Sum of Sales],SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey])))

Now the worst that can happen is that a cell in the pivot will be empty when it doesn’t make sense anyhow.

Strange Phenomena

clip_image005

When I removed the current sales and use only last Year Sales (I agree that it doesn’t make a lot of sense) all items in the two slicers show as empty.

The reason is that the protected calculation returned blank for all the items and as it was the only measure used in the pivot the verdict for each items is that it is indeed empty.

The year 2006 also disappeared from the pivot because it doesn’t have sale a year before and it is a normal behavior for the pivot to remove empty rows.

Download file here.

  Subscribe to PowerPivotPro!
X

Subscribe

This Post Has 5 Comments

  1. Nice Dany. This has been very annoying and I never really could work out how to protect from the error. This will be a good fix.

    I would love to see this built into a future version of Power Pivot.

  2. Hi Dany,

    The sales for the year of 2007 is 9,791,060 but it read 4,771,584 as last year sales for 2008 and the reason is that all the numbers beyond 20070831 were missing. Why? Could you please check it out for me? Thanks.

    Regards,

    Julian Chen

    1. Hi Dany,

      Please ignore my previous post. I’ve just figured out the reason is that no more sales after 08/31 in year 2008. By the way, I got another question: When I added DateKey from DimDate table in the pivot table rows field, I found the number for the Last Year Sales of 20080229 was duplicated from the Sum of Sales of 20070228 due to year 2008 is a leap year. Was there any measure in the data model to handle this case? Plese kindly advise. Thanks.

      A newbie to DAX

      Julian Chen

      1. One year before of 2/29/2008 is 2/28/2007
        Also the parallel day of 2/28/2008 is 2/28/2007 so you see the same value twice
        Notice the total of last year sales doesn’t double count.
        Dany

Leave a Comment or Question