Finding the Latest Date in Power BI Desktop

One common question I get is how to do date filtering in Power BI.  In a previous post, I showed how to make filters that show if a values occurred in the last 30 days, in the last month, in the last 12 months, or in the current year. 

This post show how to show the latest date.  You can read on below, or grab the data and MaxDateExample.pbix files to see it in action yourself.  If you don't have Power BI Desktop yet, you can get it here.

 

Step 1: Import your data set.

In this case I have a simple table called Table1 with a column called Date.  Just click Get Data and select the excel workbooks (Book1.xlsx).  Select Table1 and press the Load button. The data will load and you'll see fields at right on the screen; you can drag them to the canvas to create a table or chart.

 

Step 2: Create a measure to return the Latest Date

Select the table in the fields list and press the 'New Measure' button in the ribbon. Here's the formula to use:

MaxDate = CALCULATE(MAX(Table1[Date]), ALL(Table1))

I wanted to mention why I'm using CALCULATE.  In Power BI, every time you use data, it is implicitly grouped by the categories that are used.  A measure when used in a group context, computes its value only within that group by default.  This makes sense when trying to make measures that work at any level of grouping/hierarchy.  In my "latest' case, I want to get the latest value regardless of grouping.  To do that I'm using the CALCULATE (... , ALL(...)) pattern.  It's a little confusing since the ALL(...) is called a 'filter' but it's working on grouping... yikes. You can think of a group as a kind of filter that select only rows that match the group label... so it all works out.

 

 

Step 3: Add a column to the table that tells you which rows have the latest date

Select the table in the files list and press the "New Column" button in the ribbon.  Here's the formula to use:

Is Latest = if(Table1[Date] = Table1[MaxDate], "Latest", "")

This checks if the value of the "Date" column in each row exactly matches the MaxDate value computed for all rows and writes the value "Latest" if it does.  Note that if you're using date time, this includes hours, minutes, seconds in the calculation; if you just want to know if it's on a specific day, then you'd adjust the formula.

 

 

Step 4: Use the "Is Latest" column in filters or slicers

You can now drag the "Is Latest" column to the filter area at the visualization or page level to show the value you want.

 

As a bonus, Power BI now support adding date and text measures to the canvas, so you can even put MaxDate in your report without jumping through hoops.  This is useful if you'd like to show the "last updated time" for your queries.

 

HTH,

-Lukasz

Comments

• Anonymous
  June 29, 2016
  Thoughts on a measure that would give me the date in which the highest sales occurred. I have dataset columns for with the sales and another with the sales date.
  • Anonymous
    June 29, 2016
    You need to rank your sales my amount, then pick the date where the rank is 1. HTH>