PowerPivotPro

PowerPivotPro is Coming to Boston

May 15 - 17, 2018

AVAILABLE CLASSES

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

MAY 15 - 16

Foundations: Power Pivot & Power BI

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!

Overview:

  • Not just the “hard” skills, but also the “soft” stuff (when and why to use it, how to get the best results for your organization, etc.)
  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • You don’t need to be an IT professional – most of our students come from an Excel background
Boston Public Training Classes - PowerPivotPro
Boston Public Training Classes - PowerPivotPro

MAY 15 - 16

Level Up Series: Advanced DAX

Foundations taught us how to remove repetitive, manual work and make impactful insights. Advanced DAX is about making it rain money by better informing decisions!

Overview:

  • Taught completely in Power BI Desktop
  • If Foundations is a 101 course, hands-on work experience with DAX is 201, and Advanced DAX is 301.
  • This class will teach you how DAX really works, how to build complex reports that are still digestible, and how to use that information to drive your business.

MAY 17

Level Up Series: Power Query for Excel & Power BI

Copy-paste? Dragging formulas down? SAME THING EVERY WEEK?… No more. Teach your computer how to build your reports for you. Set and forget!

Overview:

  • This class will teach you how to connect to all of your data (no matter where it lives), shape it so DAX can run automagically, and have your computer remember the steps so you never have to do it again.
  • You don’t need to be an IT professional – most of our students come from an Excel background
  • Taught simultaneously in Excel and Power BI
Boston Public Training Classes - PowerPivotPro
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