Dynamic Age Calculation Using Measures

January 29, 2015

by Matt Allington

I was recently helping a forum member at http://powerpivotforum.com.au with a problem about how to dynamically calculate an employee’s age.  I thought a worked through example would make a good blog post as it demonstrates a further use of disconnected tables vs the more common “disconnected slicers” tables that Rob loves so much.

First let me explain the scenario.

Number of Employees Under the Age of 35

The requirement is to be able to calculate the total number of employees under age 35 years of age at any point in time.  The DAX formulae therefore need to take into account new employees starting at the company, employees leaving the company, as well as the fact that all employees get older every year.  Here is the solution I created. Read the rest of this entry »

Power BI, YouTube for Workbooks: More Affordable!

January 27, 2015

Post by Rob Collie

Power BI = YouTube for Workbooks, But Also Now Something Else

$9.99 Per User Per Month Starting Feb 1!

Wow, this is awesome.  What I have been calling “Power BI Online” – which is one of the easiest ways to get a Power Pivot / Excel server, aka YouTube for Workbooks, is dropping in price from $40 per month per user to $9.99 – us math folks call that a 75% price cut.

So if you’ve been “priced out” of the server market up until now, next month may be your opening.  Read the Microsoft Announcement.

And in arguably bigger news… MS announces a FREE version?

My Power BI V2 Preview Site - 100% Free Forever

This is the Preview Site That I Signed Up Today.  Took Two Minutes.
And it Will Continue to be 100% Free.

Even more interesting (perhaps) is that today MS launched a “Power BI v2” – in Preview mode.

As part of this v2, they are introducing a two-tier pricing plan: Free and Pro.  The plans can be viewed by clicking the image below:

Power BI V2 - Pricing Card

Click the Image to View the New, V2 Pricing Plans

Comparing the three versions:  V1, V2 Free, and V2 Pro

***UPDATED as of Jan 29

I think it’s fair I’ve quickly thrown together the following table based on what I understand so far:


Power BI V1

V2 – Free

V2 – Pro

Available Today?

Yes – Has Been for Awhile

In Preview (Preview is somewhat a hybrid between Free & Pro)

In Preview (Preview is somewhat a hybrid between Free & Pro)

Can You Use Excel as Your Report Surface?


No – But Maybe in the Future?  (This is a BIG question for me of course)

No – But Maybe in the Future?  (This is a BIG question for me of course)


$9.99 per user per month (but that’s on top of SharePoint Online Pricing, so actually it’s more than $9.99)

(and will remain
free even after
the Preview ends)

$9.99 per user per month (and so far, it seems like that’s an all-inclusive price, but we will wait and see)

Can you use their new Power BI Designer to build dashboards?




Does it Support Automated Refresh of Models and Reports?

Yes – Through the Data Management Gateway

Yes – But Only via SaaS data sources like SalesForce.com OR via “grabbing” workbooks from OneDrive (there is no connectivity to on-premises data sources)

Yes – everything that Free can do, PLUS connectivity to on-premises data sources via the Data Mgmt Gateway

Data Refresh Limits

1x Daily Refresh, Unclear if there is a “rows per hour” limit, but likely this becomes equivalent to Pro in the future (1x Hourly and 1M Rows per Hour)

1x Daily Refresh, maximum of 10K rows per hour.

1x Hourly Refresh, maximum of 1 Million rows per hour.

Data Capacity

250 MB per workbook, but you can seemingly upload an unlimited number of those today. Might be Reduced to Pro Level in the Future (10 GB per user – which is still A LOT.)

1 GB per User (which is quite a bit considering how much you can fit into a compressed Power Pivot model, but I think still capped at 250 MB per workbook?)

10 GB per User (but I think still capped at 250 MB per workbook?)

Can it “live connect” to on-premises SSAS servers, thus serving as a UX layer for data that remains safely inside the corporate firewall?

I Think This is Planned but Not Yet Ready




  1. It sounds like the Preview is a “mish-mash” of what Free and Pro will look like once they move from Preview to General Availability.  I’m not 100% on the details yet, but it sounds like maybe they don’t have the data refresh limits in place yet for instance, AND you can connect to your SSAS on-premises servers.  So Preview is kinda… Pro.
  2. OneDrive-based workbooks as refreshable data sources – for the time being this means “Power Pivot Data Model Workbooks” as opposed to “sheets of raw data in Excel.”  Also, it’s up to YOU to get that OneDrive workbook updated – those don’t refresh themselves as part of this service.

I will continue to update this as info becomes clearer.  And please feel free to let me know if YOU know how to clarify it in any way.

Last Note:  Never “Sleep” on Mount Redmond

Mount Redmond :)

In JRR Tolkien’s “The Silmarillion,” Mordor unveils a secret weapon – the world’s first dragon – and unleashes it on Middle Earth.

This dragon – named Glaurung – engages the forces of light, immediately gets shot full of arrows, and then retreats back into Mordor, never to be seen again.  Everyone regards Glaurung the Novelty as a joke, and forgets about this failed little experiment.

Centuries later though, Glaurung re-emerges, now MUCH older, bigger, and equipped with incredibly thick scales.  Glaurung v2 is an unstoppable juggernaut, lays waste to entire kingdoms, and takes one of those kingdoms as its lair.  Glaurung basically says “how you like me NOW?”

I see Microsoft, and honestly myself, through this lens.  Never look at their first effort and think that’s the end of it.  They are hampered by politics and Big Company inertia, for sure, but they never stop chugging.  Rust Never Sleeps, said Neil Young.  Neither does Microsoft, and neither should your perception of them.

Live Class, Online, Online U… Which is Right For You?

January 26, 2015

By Avi Singh

Few of you have inquired about the learning options now available. This may help you choose:-

  • Live In-Person Classes (Cleveland & DC): The most engaging format. Hard to replicate the energy of a live class in any other format.
  • Live Online: If you are unable to travel to an in-person class, this is a great alternative. Fully interactive and we use online chats, polls & tests to enrich the training experience
  • Online University (Recorded): 22+ hours of video! This would be equivalent to a 5+ day course but you pace yourself and can revisit topics for a full year. Greater depth and breadth than what can be covered in two days.  Often taken “standalone” or as a “supplement” to our other offerings.
  • Onsite Jumpstarts (Your Location):   We bring the class to you and your team.  For larger teams, this is more cost effective than sending everyone to the In-Person Class, plus we get to use YOUR data and biz problems as the basis of the training.  Together we build working solutions to your current problems while you learn.

    Multiple Paths All Lead to Awesome Power Smile

    A former instructor, once told me, “Every person learns in their own way”. So in the end, the choice is yours. But we encourage you, in this new year, to seize the day, grab the bull by the horns and make 2015 the year you kicked things in high gear!

    Power On!
    Avi Singh

  • SEA vs GB NFC Championship Game: Visualized in Power BI Dashboard

    January 22, 2015

    By Avi Singh [Twitter]

    For today’s post, I get to combine two of my passions – Seahawks Football and Power BI. Watch the video below where the play by play game data from the SEA vs GB game, is analyzed in Power Pivot and visualized in the new Power BI Dashboard Preview.

    Here are some of the highlights from the video, you can click these images to jump to that section in the video. Look out for some special guest star appearances in the video around the 30 second mark!

    Click to subscribe to us on YouTube

    Video Highlights

    1. Pulling and Parsing Play by Play game data: Gosh this was hard. I threw the kitchen-sink at it, using Power Query, regular Excel and VBA. That is the part that I like about Power BI though. You do not have to chuck everything you have learned so far. I am able to weave in all my existing skills around Excel and SQL when I use Power BI.
    2. Building the data model. Pretty basic and quite flat. Learn how not to be flat.
    3. Analysis of the game using Power Pivot measures (Excel Dashboard)
      I have real respect for all the number crunching that goes on in NFL. It ain’t easy. Fans can check out Rob’s Great Football Project.
    4. Football Earthquake: Yup, the seismologists can hear the 12th man!
    5. Power BI Dashboard Preview: Introduction
    6. Power BI Dashboard: Treemap
    7. Power BI Dashboard: Gauge
    8. Power BI Dashboard: Combo Chart
    9. Power BI Dashboard: Scatter Chart. Charts Reception Yards by Height and Weight of the players

    The million (billion?) dollar question

    How does it all fit in, in Microsoft’s vision and roadmap?There would be Power BI users on Office 2010, Office 2013, Office 365, upcoming Office 15 and the Power BI cloud service. The new release has even struck out of the Office camp in some ways, by releasing a stand-alone Power BI Designer. That can be a good thing, but being an Excel user at heart, leaves me wondering where things are headed. Either way, this promises to be a rollicking ride!

    Download the file here.

    Power On!
    Avi Singh

    The Only “Requirements Doc” You Will Ever Need

    January 20, 2015

    Post by Rob Collie


    One of Our Clients Sent This to us Before a Consult.  It is Perfection.

    The Dysfunctional Myth of “Requirements Discovery”

    A traditional BI project typically starts with “requirements discovery.”  This is where YOU, the business, get to spend multiple days, weeks, or even months teaching someone else (a BI consultant or internal BI pro) about your business.

    There are MULTIPLE problems with this traditional methodology:

    1. YOU are the teacher (teaching about your biz), but YOU are paying.  Seems backwards yes?  Money usually flows in the opposite direction of knowledge transfer.  But not in BI.
    2. YOUR time is a MASSIVE hidden cost.  In addition to the fees you may be paying, don’t forget that YOUR time is being consumed in the process.  If we were going to put an “honest” cost on a BI project, the time consumed by Business personnel should be included.  (And that’s a lot more than your salaries – there’s opportunity cost to the business as well since you aren’t doing OTHER things).
    3. When the dust settles on “discovery,” the BI Pro STILL does not understand.  Oh, everyone PRETENDS that “discovery” is over, but really, it’s just begun.  In a few weeks, when the first “results” come in from the BI Pro, you will then, ahem, discover that there were tremendous misunderstandings.

    This dysfunction is the root of BI failure.  Projects that never end.  Projects that end, but under-deliver.  And also…  projects that never even start.  If you’ve ever said “we can’t afford BI,” you were both simultaneously correct AND unknowingly reacting to this dynamic.

    This dysfunction is NOT your fault, NOR is it the BI Pro’s fault.  You see, we’re terribly ineffective at communication, us humans – both on the “send” side AND the “receive” side.  No exceptions.

    Requirements Discovery Works MUCH Better on Planet VulcanThe people involved are FINE.  It’s the methodology that’s broken.  I don’t have a single critical word for the PEOPLE involved in such projects.  We’d need to be Vulcans, equipped with the Mind Meld power, to make this approach work well.

    So WHY did a bad methodology gain acceptance in the first place?  Because the software vendors did this TO the world.  Perpetrated it ON the world.  They built software that REQUIRED this sort of methodology.  All of the big players share historical blame here – Microsoft, IBM, Cognos, Business Objects, Microstrategy.  All of them.  Let’s shine a light on them.

    Our Villain:  The Ivory Tower “Arrogance” of Past BI Software

    Read the rest of this entry »

    Reminder: Webinar – Excel to Power Pivot Jan 20th (mini-post)

    January 19, 2015

    Folks, our Webinar: Excel to Power Pivot, is coming up today Tuesday Jan 20th (click to add to calendar). We hope to see you there. Participants would also have an opportunity to sign-up for our online class at a discounted rate.

    Here is what we would cover, see event page for more details:-

    Walkthrough key steps in using Power Pivot and explain how Power Pivot supercharges regular Excel
    Do it Yourself! We would provide you dataset and instructions to try those steps yourself at your own pace and build your first data model. Best way to learn is by doing.
    Q&A: There would be opportunity to ask questions and seek guidance regarding using or deploying Power Pivot or Power BI
    Resources to continue your journey

    Excel Power Pivot steps we would cover in our walkthrough:-

    Pull data into Power Pivot
    Build a Data Model
    Write new Measures (Formulas)
    Write time-intelligence Measures

    Subscribe to our blog to receive updates on future events.

    Power On!
    Avi Singh

    Power Query for Excel: Combine multiple files of different file types

    January 15, 2015

    Guest Post by Miguel Escobar - Twitter | Youtube | Blog | Website

    Power Query Magic:  The Ultimate and easiest way to consolidate multiple tables, sheets, text and/or csv files

    Power Query Magic:  The Ultimate and easiest way to consolidate multiple tables, sheets, text and/or csv files
    (Click for Full-Size Version)

    At some point in the life of an Excel user, we have all faced a similar dillemma. How can I combine multiple sheets, tables, csv or txt files? (can I combine them all together??)

    How we used to solve this scenario

    Back in the day (before Power Query) we actually had some ways to do so but they were not so user-friendly and they relied heavily on coding or some tedious way of doing it. The most common ways were:

    1. Using SQL Statements to join multiple files
    2. Creating a VBA code that will do the job for me
    3. Going with the tedious way of combining the files manually (perhaps with Excel or Access)

    But now we have an easier and optimized way of doing this..let’s find out how

    Read the rest of this entry »

    Power BI as Google Docs Antidote

    January 13, 2015


    “Help ME (Power BI)…  Help YOU (Office).”

    Reviving the “Open Letter to MS” Tradition

    In the past, I’ve written some things aimed at my former Microsoft colleagues.  Places where I think their strategy could benefit from adjustment.  Generally speaking, those posts have been about how the “BI teams” at MS should better leverage their Excel advantage.  Given recent developments, I think those messages are more important than ever.

    But today, I am reversing that lens, and talking instead about how the Power BI suite of tools is a tremendous gift to the Office team.  Aha!  Bet you didn’t expect THIS dramatic turning of the tables from Rob “Excel is Everything” Collie, DID YOU??  Gotta stay on your toes around here.

    You Don’t Have “Users.”  You Have “Producers” and “Consumers!”


    I think the word “user” is responsible for a lot of strategic damage.  It lumps everyone into one big, convenient bucket – hiding some crucial, underlying dynamics.

    Read the rest of this entry »

    PowerPivot Live Online Class in January and New Year Resolutions

    January 12, 2015

    By Avi Singh [Twitter]

    We wish all the readers of PowerPivotPro a very happy new year. We wish that you get your groove power on this year :-). I have never been big on New Year’s resolutions. For good reason; reportedly only 8% of people achieve their New Year’s resolution. Sharing your resolution socially and publicly, is one factor that could help you get there though (check out other factors).

    So here I am, going out on a limb and saying:

    I will help a thousand people to start using Power Pivot

    That is my BHAG (Big Hairy Audacious Goal) for the year. I had a late start in 2014, but here is how many I helped last year (via training, webinar, workshops, consulting).

    Here is a geographical representation. Yes that’s an embedded Power View (view full screen); more on that in a later post.

    In order to get to this goal, we will continue existing offerings but also add some new ones:-

  • Live In-Person classes
  • Online University
  • Consulting
  • Webinars
  • *New* Live Online Class

      New! Live Online Class in January

      I am excited to offer our first live online class. Click below for details and registration.
      There are limited seats for first online class as we ramp this up.

      Read the rest of this entry »

    • Cleveland and D.C. In-Person Classes in February!

      January 8, 2015

      Post by Rob Collie

      How recent students have described the class


      One of Our 2014 Classes

      Washington D.C. Feb 24-25 and Cleveland Feb 17-18

      Washington DC Class Info

      Register for Washington DC Class!

      Cleveland OH Class Info

      Register for Cleveland OH Class!

      Optional Gathering/Dinner on “Middle” Night

      Read the rest of this entry »

      Would You Like to Join Our Team in 2015?

      January 6, 2015

      Post by Rob Collie


      Do You Have the “Data Gene?”  Know Your Way Around Power Pivot?
      We’re Looking for You in 2015!

      Hi folks, we’ve done this a number of times on behalf of our clients, but never before have we solicited resumes for PowerPivotPro itself.

      Well, I think that changes in 2015.

      Now, to be perfectly clear, we’re not hiring immediately, nor are we 100% certain yet on what kinds of positions we will be filling.  (Nor how many team members we will add).

      But I’m confident that we WILL be growing the team this year, and I’d like to start getting a sense of who is out there that would be interested.

      So, if you’re even curious about what this might look like, click below and drop us a quick note:

      Me?  Yeah, I Like Changing the World!  :)

      Mini-Post: Excel to Power Pivot, Webinar Jan 2015

      January 5, 2015

      by Avi Singh [Twitter]

      Pop quiz: For 2015, do you
      a) change nothing this year?
      b) Embark on a brand new path of empowered BI?

      Calling out to folks who are sitting on the Excel fence and have not dipped their toe in Power Pivot.
      Or business groups that want to explore a whole new way of doing BI, that is low cost, agile and delivers results.

      Join us for our next Webinar: Excel to Power Pivot: Cross the Gap, Tuesday Jan 20th 2015

      Click here for Webinar Details

      For many readers who have already crossed the gap, please forward this to those who need help.
      We did face some technical difficulties in our first attempt on Dec 1st, but since then have switched to using GoToMeeting – world leader in web conferencing – and it has performed superbly. Thanks for bearing with us as we experiment with this new format.