BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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 | |
| 1001 | 20/04/2016 | |
| 1002 | 07/04/2016 | Other |
| 1003 | 19/05/2016 |
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:
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!