skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile


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.

Employee Database

I have used the Adventure Works Employee database from Microsoft – it has a total of 290 employees on record.  Each employee record contains a birth date, a hire date and a quit date (assuming they have quit already).

So my source data looks like this.

image

Calendar Table

I also created a calendar table with all possible dates starting from the date when the first employee was hired (1996).  There is no need for the date table to go back in time to when the first employee was born because I only need to calculate their age when they were actually employed.

image

The Tables Should not be Linked

For this scenario, there is no need to link the tables together.  There are actually 3 date fields in the Employee table and if you link one or more of these data fields to the Calendar table you will actually get the wrong behaviour.  In this case we don’t want to propagate filters from the Calendar table to the Employee table.  The Calendar table is therefore loaded as a disconnected table – its sole purpose for existence is so that we can “harvest” the date/month/year data to populate our pivot table rows/columns/slicers as needed.  More on that later.

So our 2 tables look like this – loaded but disconnected.

image

Building the Measures

I built a couple of helper measures that can be used by themselves, but can also be used as inputs to other measures.  I like building my DAX this way as it breaks the process of creating calculations down into small manageable pieces, and that makes the whole process easier to work through.

True Quit Date

First I created a [Calculated Quit Date] helper measure.  If you look at the data in the [QuitDate] field, it either contains a date (if the employee has left the company already) or it is left blank (if the employee is still working at the company).  The [Number of Employees] measure (shown further below) needs a true date value (not a blank), so this [Calculated Quit Date] measure simply replaces all the blank values with the date 9/9/9999.

Calculated Quit Date :=
IF (
    ISBLANK ( MAX ( Employee[QuitDate] ) ),
    DATEVALUE ( “9/9/9999” ),
    MAX ( Employee[QuitDate] )
)

Number of Employees

The next thing I did was to create a raw calculation of the total number of employees.  To do this I used CALCULATE( ) with a FILTER( ) function to filter the employee table to exclude employees that haven’t joined the company as yet, and to also exclude those that have already left.

Number of Employees :=
CALCULATE (
    COUNTROWS ( Employee ),
    FILTER (
        Employee,
        Employee[HireDate] <= MAX ( Calendar[Date] )
            && [Calculated Quit Date] >= MAX ( Calendar[Date] )
    )
)

In the measure above you can see where I am “harvesting” the date data from the Calendar table using the MAX( ) function.  When you wrap a column in MAX( ) in this way, DAX will respect the current filter context coming from your pivot table.  In the sample pivot table below, I have put Calendar[Year] on rows.  So the MAX( ) function “harvests” the maximum date in the Calendar table after it is filtered for the specified year by the Pivot Table.

Using 1998 as an example, the Pivot Fable first filters the Calendar table to only contain dates from 1/1/1998 to 31/12/1998.  The MAX ( ) function then returns 31/12/1998 to the measure above. Finally the Employee table is filtered to find all employees that had a [HireDate] before 31/12/1998 and had a [QuitDate] after 31/12/1998.  This approach makes the measure dynamic and sensitive to the selected dates.

image

Now of course you could argue that you should include employees that started after 1/1/1998 and that would be a valid approach – it really depends how you want to do the calculation.  My calculation is a snapshot at the end of the period however if you wanted to include employees that started after 1/1/1998 you would simply change the formula to use:

Employee[HireDate] <= MIN ( Calendar[Date] )

Employee’s Age

The next step is to create a generic measure that calculates the employee’s age.  I have used the same harvesting technique here to take the date displayed in the pivot table and then subtract the date the employee was born.  This gives the total “days old” for each employee, so dividing by 365 will calculate the age in years.  The same comment as above applies here – this is their age at the end of the period (in this case at the end of the year).

Employee’s Age :=
 ( MAX ( Calendar[Date] ) –  MAX ( Employee[BirthDate] )  / 365

It is probably worth pointing out here that this measure will only work correctly when there is a row context or when the pivot table is filtered for an individual employee (eg if they employee names are on rows).  But we are going to use this measure inside a FILTER ( ) function, and FILTER is an iterator that creates its own row context – so there is no problem here.

Number of Employees Under 35 Years Old

In the final step, I built a new measure that recalculates the [Number of Employees] measure but first filters out those that are under 35.  This calculation is quite straight forward given I have built the helper measures along the way.

Employees < 35 :=
CALCULATE (
    [Number of Employees],
    FILTER ( Employee, [Employee’s Age] < 35 )
)

The Benefit of Helper Measures

The [Employees < 35] measure above using helper measures is easy to write and is easy to read/understand.  Compare the above version of this measure to the version below that doesn’t use any helper measures.  They both give exactly the same answer using exactly the same calculations, however I am sure you will agree that using helper measures makes the entire process easier to read and write.

Employees < 35 Complex:=
CALCULATE (
    COUNTROWS ( Employee ),
    FILTER (
        Employee,
        CALCULATE (
            (
                ( MAX ( Calendar[Date] )MAX ( Employee[BirthDate] ) )
                    / 365
            )
                < 35
        )
    ),
    FILTER (
        Employee,
        Employee[HireDate] <= MAX ( Calendar[Date] )
            && [Calculated Quit Date] >= MAX ( Calendar[Date] )
    )
)

And the Final Result

The final result is a pivot table that allows you to bring in calendar information into rows, columns and/or slicers and get dynamic calculations of the age profile of employees over time.

image

I have attached the workbook I used for this post here (Excel 2013 format).


 

Matt Allington is a professional Self Service BI expert, consultant and trainer based in Sydney Australia.

Matt Allington

Matt Allington is a Microsoft MVP specalising in Power Pivot, Power BI and Power Query Consulting and Training based in Sydney Australia. Visit Matt's blog here.

This Post Has 13 Comments
  1. I guess you are aware that your Employee’s Age measure is not precise.

    e.g. EmployeeID 217:

    born 01/01/1975
    year end 12/31/1997
    days elapsed 8400
    days/365 = 23,01369863

    The correct age is of course 22.

    1. Yes you are right. the formula should subtract 1 from the answer. Technically the person in your example is in their “23rd year” but is actually referred to as being “22 year’s old”).

      Employee’s Age :=
      (( MAX ( Calendar[Date] ) – MAX ( Employee[BirthDate] ) / 365 ) -1

      1. Great post! I typically use YEARFRAC to calculate ages. Are there any advantages/disadvantages you see comparing this method with yours?

        Age1 :=
        ( MAX ( Calendar[Date] ) – MAX ( Employee[BirthDate] ) / 365

        Age2 :=
        YEARFRAC ( MAX ( Employee[BirthDate] ), MAX ( Calendar[Date] ) )

        On the Age2 measure you can even wrap it in a FLOOR(Age2,1) or TRUNC(Age2) function to drop the fractional digits. After all, most 35-year old adults don’t boast that they are 35 and 1/2 years old.

          1. Thanks for a good guide Matt!
            After testing this on our 1000+ employees and wanting to have ages per month i found that the “divide by 365” doesn’t give good enough results. Birthdays get moved several days, you have to at least use 365,225 which improved the result a lot, but i still got some errors.

            YEARFRAC removed all errors 🙂

  2. Great post, I like to calculate the average age of all employees, but I am not able to sum up the total of employee’s age, using the measure above Employee’s. How do I sum the total Age for all employees? I am Building a HR Dashboard and are look for measure, to use in the Dashboard.

    1. I solved the problem by using sumx

      My Measure look like this

      Average Age:=sumx(FILTER(‘DM_HR vDimMedarbejder’;’DM_HR vDimMedarbejder'[Seniority Date] = MAX(Kalender[Date]));((MAX(Kalender[Date])-‘DM_HR vDimMedarbejder'[Birth Date])/365)-1) / by the measures for Number of Employees as descript in the blog .

      It sure could be more polished

  3. Could you change the formula to represent employees BETWEEN 2 ages? For example: instead of just Employees > 35, could you get Employees Age 35-65?

  4. Really Great Post!

    If I’d like to show number of employees by year for all age groups(e.g. 50), instead of making 5 measures, is it possible to do it with one measure and a slicer to filter age group?

Leave a Comment or Question