If you found this blog useful and you’d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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):

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:

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:

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:

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

You should now get a table in PowerPivot:

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:

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):

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:

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:

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!

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

And that's it!

This blog has 0 threads Add post