How to pick out the first and last dates for a given group using DAX
It's often challenging working out how to use DAX to solve specific problems. This blog shows how to produce a pivot table showing the first and last instances of any category.

Posted by Andy Brown on 06 October 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Summarising data using DAX by the first and last date

This blog is in response to a specific enquiry from someone on one of our recent Excel Power BI training courses, but it may well be of general interest!

The problem

Suppose you have the following data (you can download this by clicking on this link, if you want to try the answer given yourself):

Table of data

Events happening for a given "app" (whatever that may be) on given dates.

 

This example is relevant for any process which goes through a series of stages (whether this be patient flow in a hospital, product review or - as for the client in this example - financial authorisation).

From this, you want to create a pivot table like this:

Pivot table

You want to show how many events occurred on the first and/or last date for each contact type.

 

To see where the numbers come from, here are the figures for each app:

App First date Contact type for this date
1000 19/10/2016 Email
1001 20/04/2016 Email
1002 07/04/2016 Other
1003 19/05/2016 Email

Creating a PowerPivot model from the table

To start the process, click on any cell in the table, and choose to add it to a PowerPivot data model:

Add to data model

Choose the Power Pivot tab, then the Add to Data Model icon (if you don't have this tab visible, you need to step back a bit).

Tick the box to say that your table has headers:

My table has headers

Your table does have headers, so tick the box and select OK.

 

You should now get a table in PowerPivot:

PowerPivot table

The table in your model should look something like this.

Using DAX formulae to solve the problem

Now create a calculated column in PowerPivot to show for each row the minimum date for this app:

DAX minimum date formula

The formula works out the minimum of the dates for each app (and is explained below).

To understand what this formula does, look at this bit in the middle:

FILTER(Data,[AppId]=EARLIER([AppId]))

What this does for any row is to take a subset of the entire table, picking out only those rows where the app id equals the one for this row.

The EARLIER function is surely the most confusing in DAX, and is explained in more detail in this blog.

You can now create another calculated column which determines for each row whether it's the first in date order for its app id (I could have combined the two columns into a single calculation, but it's clearer if we do it in two stages):

Is this the first date?

The formula returns First date for each row if (and only if) the date equals the minimum date for this app id.

Creating a pivot table

You can now choose to create a pivot table based on this table:

Creating a pviot table

Choose to create a pivot table like this.

Note that I've also created MaxDate and IfLast columns in the data model, using the same technique.

You could orientate your pivot table like this:

Pivot table fields

Here we're showing the number of rows for each contact type, with the IfFirst column as a filter.

 

You can then apply a filter to show the first date data!

First date filter

Click on the symbol shown to show only data for the first date.

 

And that's it! 

This blog has 0 threads Add post