THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
 in Alberto Ferrari (Entire Site) Search

# PowerPivot: Counting active days

Browsing on the web I have seen an interesting question that is worth a post, since I think it is a very common pattern and a good example of “thinking in DAX”.

Starting from a table which contains a people and two dates (ActiveFrom, ActiveTo), we want to compute how many days an individual has been active over some period of time, let us say at the month level.

As always, a picture is worth a thousand words, here is the input and the desired result:

The solution is very easy by means of using a calendar table. To create one, it is enough to create an Excel table like the following one and use “Create Linked Table” from the PowerPivot tab of the Ribbon.

Once linked in PowerPivot, you end up with the two tables, that I called Activities and Calendar. Beware that there is no need at all to create relationships between the two tables, we will solve the scenario using DAX only. Moreover, there is no means to create these relationships, since the dates represent a time period, not singular dates.

To reach the desired result, it is enough to note that, for each record in the Activity table, the number of rows in the calendar table that are between ActiveFrom and ActiveTo, represent the total number of activity days. Thus, the formula is straightforward:

```=SUMX (
Activities,
CALCULATE (
COUNTROWS (Calendar),
Calendar[Date] >= EARLIER (Activities[ActiveFrom]),
Calendar[Date] <= EARLIER (Activities[ActiveTo])
)
)```

This formula seems to always compute, for each row in the activities table, the total number of active days for the individual, since it does not take into account years and months. Surprisingly, if you write this code in a measure and put it on a PivotTable, you will get the result shown at the beginning.

The reason for which the formula works is that the inner CALCULATE creates a filter on the Calendar[Date] but does not change the current filter context on year and month. Thus, for each cell, the filter on year and month is still active and produces the correct result. Moreover, the same formula work for any calendar period without any change.

I guess for PowerPivot newbies this behavior seems like magic, at least it was so for me a few months ago… in reality, understanding PowerPivot is just a matter of understanding row and filter contexts, once you get them formulas are really simple to write.

An interesting exercise, left to the reader, is to count the number of working days of activity. Easier than you might think and surely worth spending some time if you want to learn some DAX data modeling.

Published Thursday, May 12, 2011 12:27 AM by AlbertoFerrari

great example of "thinking in DAX"

May 12, 2011 4:28 AM

#### incognito007 said:

It is pretty easy to calculate the number of working days considering the Calendar Table with a Working-day column and a Public-holiday column in DAX.

May 20, 2011 2:03 PM

#### AlbertoFerrari said:

@incognito

Yes, indeed.

May 20, 2011 2:51 PM

#### Barbara Raney said:

Have I told you lately how much you ROCK!  I have been working on a "Days Event Outstanding" measure for a while, and came across this.  You lead me directly to the answer I needed!

Thanks!!

February 5, 2015 1:32 PM

#### CMcReynolds said:

Despite my reluctance to build a calendar table, I did the above and was excited that it worked - but only "almost"

So I have two questions based on multiple rows with the same person:

1. If there are different dates, how do you count from the first of the start days (your [ActiveFrom]) to the last of your end days (your [ActiveTo])

2. I'm filtering by person, but I also need NOT count multiples of exact same dates (I'm looking at university term start and end dates, so I don't want to include the same count if a person takes more than one course in the same year. Also, each course is a row.)

I believe that the difference between my two questions would be 1. is calendar days and 2. is days per term.

March 5, 2015 4:36 PM

#### Mimi said:

Hello,

If there is a required goal per day per person, is there is way to calculate the total goals required per person between their hire and end date?

I have been trying to get my formula to work but I can't seem to get the subtotals/ grandtotals to only add the days for when the person was active.

Any insight would be awesome! Thank you in advance!

July 23, 2015 4:04 PM

#### Ricardo Diaz said:

The Dax formula takes a lot of time for a large database.  Is there a way to do the same without the aggregate function? Maybe using the Filter function?

January 12, 2016 12:50 PM
New Comments to this post are disabled